Need help with syntax for DoCmd.FindRecord

[jketcher]

I am new to developing VBA code and need some expert recommendations. I have two bound combo boxes that are based upon non-key fields. One holds a list of clients of which one will be selected by the user (Client). The other holds a list of Categories for entering findings (Category). A client can have multiple categories but only one of each category in the list. I need to have an edit that checks the combo box entries and either 'finds' or 'looks up' (which is best to use in this example) the data in a table. I will then display an error message if the client/category is already in the table. Can someone help me with the syntax of this code? I appreciate your help!

Thanks, jketcher

 

[MarkK]

Welcome to the forum.
You might use the BeforeUpdate event of the Category Combo.
And Check out the DCount() function. It'll count records in a table that satisfy certain criteria. If the criteria specified in the current record already exist in the table--the case where your DCount() > 0--then cancel the update.

 

[ezfriend]

See if this will help you.

http://www.databasedev.co.uk/combo_box_search.html

 

[jketcher]

I entered the following code and received an error message.
Private Sub category_AfterUpdate()
DoCmd.ShowAllRecords
Me!Category.SetFocus
DoCmd.FindRecord Me!Category
Me!Category.Value = ""
If Not IsNull(Me.Category) Then
MsgBox "Category already entered, select Edit tab to update Category."
Category.BorderColor = vbRed
Category.SetFocus
End If

End Sub

The error message:
Run-Time error '2162':
A macro set to one of the current field's properties failed because of an error in a FindRecord action argument.

I did not design this database so did not get to select the combo box or field names for the tables. The designer used 'category' (note lower case) for the name of the combo box. The field name in the table is 'Category'. Is this the problem? When I try to make 'Category' lower case Access automatically changes it to upper case. Do I need more qualifiers to get to this combo box for the Find Record statement?

 

[MarkK]

Are you trying to find a record or to validate updated data?
You mention that your combos are bound, so changing them will modify data, but your code looks like you want to find a record. Can you say more about what you are trying to do?

 

[jketcher]

I am trying to set up an edit that will return a message if a client/category combination is already in the table. The user selects a client - no problem with this. Then selects a 'category' from a list of about 150 categories. The problem is that the client and category fields are not keys in the table so the table could accept multiple entries for the same category per client. Don't want that to happen. What is the easiest way to set up this edit that will stop the user from entering duplicate data.

 

[MarkK]

So follow this logic. Prior to accepting an edit, you need to count how many records exist in the table that satisfy the criteria that the pending edit would produce.
To do this, you can, among other things, use the DCount() function which counts records in a table based on criteria you provide.
If this count returns a zero, then there are no records that satisfy your proposed edit and you accept the edit. If this count returns a one you don't allow the edit to succeed.
So code for this might look like...

private sub category_beforeupdate(cancel as integer)
  if DCount("*", "YourTable", "ID = " & me.ID & " AND Category = " & me.category) > 0 then
    'a record already exist for this case so cancel the update and inform user
    cancel = true
    msgbox "would create a duplicate record."
  end if
end sub

You can also create an index in the table that requires that a combination of fields always be unique. In this case a trappable error will occur if you attempt to add or edit a record such that it violates this uniqueness.

Another option is that you base your category combo on a query that only returns valid options.

Cheers,

 

[jketcher]

I entered the code using the model you recommended (by the way thank you so much for all of your help). It resulted in a syntax error: (missing operator) in query expression 'Client = xCollege AND Category = Application and Verification: Correcting Errors'.

The good news is it is picking up the fields to use for the criteria correctly. Here is what I entered. Please advise.

Private Sub category_beforeupdate(cancel As Integer)
If DCount("*", "Operational Review Findings", "Client = " & Me.Client & " AND Category = " & Me.Category) > 0 Then
cancel = True
MsgBox "Would create a duplicate record." etc.

I tried several things but nothing worked to get rid of the errors. Should there be a quote after Me.Category? or an &? I wish I understood how the syntax for VBA works. I am an x COBOL programmer so need to study up on VBA syntax.

 

[MarkK]

Ya, so now, with your criteria, if Client or Category are string fields then the data being compared needs to be enclosed in quotes...

"Client = '" & Me.Client & "' AND Category = '" & Me.Category & "'"

 

[jketcher]

It WORKED!!! Thank you so much. Jketcher