Table check from Form

[Abhean]

Ok I am not good at forming my questions. I am attempting to check for existing records from a table (Client) specifically from (CAccount) field on that table. The form I am working in is based on another table (DoSService). The txtfiels on the form is (DoSLocation).

I keep getting an error at the .findfirst line. What am I forgetting or missing?

Private Sub DoSLocation_AfterUpdate()
Dim db      As DAO.Database
Dim rst     As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Client", dbOpenDynaset)
 
    With rst
           If Not (.BOF And .EOF) Then
               Call .MoveFirst
               .FindFirst "CAccount = '" & Me!DoSLocation & "'"
               If Not .NoMatch Then
                   'Found it!
                   MsgBox "found the record"
               Else
                    MsgBox "Did not found the record"
                    'DoCmd.OpenForm "Client_Frm", acNormal, , , acFormAdd
                   'Call .Update
               End If
           End If
           Call .Close
       End With
 
End Sub 

[twinnyfo]

Abhean,

First, what is the error that you are receiving? That is more instructive to us than anything else.

Second, it might be as simple as replacing line 10 above with:

.FindFirst "CAccount = '" & Me.DoSLocation & "'"

(replace the bang (!) with the dot (.). In some instances either delineator will work. This might be one of those cases in which it does not.

Thirdly, if CAccount is a numerical field, you will throw an error as you are trying to search for a text value. In that case, your syntax should be:

.FindFirst "CAccount = " & Me.DoSLocation

Finally, some free advice. It is always wise to rename the controls on your form to a different name than the fields that underlie them. This can avoid ambiguity--and Access loves things to be more explicit in most cases.

Hope this gives you a few options to try.

Hope it hepps!

[Abhean]

It is telling me Datatype mismatch in criteria expression.
both data sets are number.

BAM! the 2nd code snippet worked like a dream.

And thanks for the advice. I am having to relearn access once again. :)

[NeoPa]

The error message is nearly always the most helpful information in a question.

There doesn't appear to be anything fundamentally wrong with the syntax of your code. Obviously the criteria used has to match the Tables and Fields as defined in your database so we would need the detail of that before we could properly check that the format of the criteria makes sense for your situation.

I suspect with that information available any issues would be easily resolvable.

[NeoPa]

Ah. That makes sense.

That error message is telling you that the type of the comparison value (Text) is not compatible with the referenced Field. That indicates that [CAccount] must be non-textual. Numeric or date/time.

The fact that Twinny's suggested code works confirms the Field is numeric.

You may find Quotes (') and Double-Quotes (") - Where and When to use them to be helpful in order to get a good understanding of why certain ways of doing things are necessary.