SQL with Manoj

Good code except for the fact that if there is an error in column “A” but no error in column “B”, error code gets reset to 0, and no error message is displayed. You could set a Boolean to True if errorcode = 1 after checking each field, and then check the Boolean at the end to decide whether to display the error message.

looking at this code, whats the best way to condense this code so its not so long?

Option Compare Database

‘Step 1 Setting up connection for access to vba

Dim connection As New ADODB.connection
Dim magazines As New ADODB.Recordset
Private recordBookmark As Integer

‘Step 2 Create your function for populating the form

Private Sub PopulateForm()

Me.txtMagazineID = magazines.Fields(“magazineId”)
Me.txtName = magazines.Fields(“name”)
Me.txtDate = magazines.Fields(“firstPublished”)
Me.txtFee = magazines.Fields(“subscriptionFee”)
Me.txtCategory.Value = magazines.Fields(“categoryId”)

End Sub

‘Step 2.1 Creat your lock form

Private Sub LockForm()

Me.btnAdd.Enabled = True
Me.btnEdit.Enabled = True
Me.btnSave.Enabled = False
Me.btnCancel.Enabled = False
Me.btnExit.Enabled = True
Me.btnDelete.Enabled = True

Me.txtCategory.Locked = True
Me.txtMagazineID.Locked = True
Me.txtName.Locked = True
Me.txtDate.Locked = True
Me.txtFee.Locked = True

End Sub

‘Step 2.2 Creat your edit unlock form

Private Sub EnableLockForm()

Me.btnAdd.Enabled = False
Me.btnEdit.Enabled = False
Me.btnSave.Enabled = True
Me.btnCancel.Enabled = True
Me.btnExit.Enabled = True
Me.btnDelete.Enabled = False

Me.txtCategory.Locked = False
Me.txtMagazineID.Locked = False
Me.txtName.Locked = False
Me.txtDate.Locked = False
Me.txtFee.Locked = False

End Sub

‘Step 3 load form

Private Sub Form_load()

Set connection = CurrentProject.connection
magazines.Open “SELECT * FROM magazine ORDER BY magazineId”, connection, adOpenKeyset, adLockOptimistic
magazines.MoveFirst

Call PopulateForm
Call LockForm
End Sub

‘Step 4 Create list before creating search and so it can link properly

Private Sub txtList_Click()
magazines.Requery
magazines.Find “magazineId = ” & Me.txtList.Value
Call PopulateForm

End Sub

‘Step 4.2 This part comes last when your list is done, its because its the same code(optional)

Private Sub btnSearch_Click()

magazines.Requery
magazines.Find “name LIKE ‘” & Me.txtSearch & “%'”
Call PopulateForm

End Sub

‘Step 5 making your add button working

Private Sub btnAdd_Click()

Call EnableLockForm

magazines.AddNew
magazines(“name”) = “”
Call PopulateForm

End Sub

‘Step 5.1 Making edit button

Private Sub btnEdit_Click()

Call EnableLockForm
magazines.Requery

End Sub

‘Step 5.1.5 making the save update form

Private Sub UpdateForm()

magazines(“categoryId”) = Me.txtCategory
magazines(“name”) = Me.txtName
magazines(“firstPublished”) = Me.txtDate
magazines(“subscriptionFee”) = Me.txtFee

End Sub

‘getting the validation to work
Private Sub validation()

Dim errors As String
errors = “”
Dim savedCustomer As String

If Len(Me.txtName) = 0 Then
If errors = “” Then
Me.txtName.SetFocus
End If
errors = errors & vbCrLf & “Please enter name”
End If

If IsNull(txtFee) > 9 Or Not IsNumeric(txtFee) Then
If errors = “” Then
Me.txtFee.SetFocus
End If
errors = errors & vbCrLf & “Number must be greater then 9”
End If

If IsNull(txtDate) Or Not IsDate(txtDate) Then
If errors = “” Then
Me.txtDate.SetFocus
End If
errors = errors & vbCrLf & “ou must enter a correct date e.g 1/1/2017”
End If

If errors = “” Then
recordBookmark = Me.txtMagazineID
Call UpdateForm

magazines.Update
magazines.Requery

magazines.Find ” magazineId = ” & recordBookmark

Call LockForm
Else
‘if there are errors, shows error msg and prompts a cancel
MsgBox (errors)

End If

End Sub

‘Step 5.2 Making the save button

Private Sub btnSave_Click()
Call validation

End Sub

‘Step 5.2 Making the cancel button

Private Sub btnCancel_Click()

magazines.CancelUpdate
Call PopulateForm
Call LockForm

End Sub

‘Step 5.2 Making the delete button

Private Sub btnDelete_Click()

Dim confirm As Boolean
confirm = MsgBox(“Confirm Delete of Magazine: ” & Me.txtName, vbYesNo)
If confirm = True Then
magazines.Delete
magazines.MovePrevious
Call PopulateForm
End If

End Sub

Private Sub btnFirst_Click()
magazines.MoveFirst
Call PopulateForm

End Sub

Private Sub btnLast_Click()

magazines.MoveLast
Call PopulateForm

End Sub

Private Sub btnNext_Click()

On Error GoTo btnNext_Click_Err

If magazines.EOF Then
    magazines.MoveLast
Else
    magazines.MoveNext
    Call PopulateForm
End If

btnNext_Click_Exit:
Exit Sub

btnNext_Click_Err:
MsgBox “This is the last customer on record.”
Resume btnNext_Click_Exit

End Sub

Private Sub btnPrevious_Click()

On Error GoTo btnNext_Click_Err

If magazines.BOF Then
    magazines.MoveFirst
Else
    magazines.MovePrevious
    Call PopulateForm
End If

btnNext_Click_Exit:
Exit Sub

btnNext_Click_Err:
MsgBox “This is the last customer on record.”
Resume btnNext_Click_Exit

End Sub

Private Sub btnExit_Click()

DoCmd.Close
‘SELECT category.categoryId, category.description FROM category ORDER BY categoryId;
End Sub