By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,067 Members | 872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,067 IT Pros & Developers. It's quick & easy.

Table check from Form

Abhean
P: 28
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub DoSLocation_AfterUpdate()
  2. Dim db      As DAO.Database
  3. Dim rst     As DAO.Recordset
  4. Set db = CurrentDb()
  5. Set rst = db.OpenRecordset("Client", dbOpenDynaset)
  6.  
  7.     With rst
  8.            If Not (.BOF And .EOF) Then
  9.                Call .MoveFirst
  10.                .FindFirst "CAccount = '" & Me!DoSLocation & "'"
  11.                If Not .NoMatch Then
  12.                    'Found it!
  13.                    MsgBox "found the record"
  14.                Else
  15.                     MsgBox "Did not found the record"
  16.                     'DoCmd.OpenForm "Client_Frm", acNormal, , , acFormAdd
  17.                    'Call .Update
  18.                End If
  19.            End If
  20.            Call .Close
  21.        End With
  22.  
  23. End Sub 
3 Weeks Ago #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. .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:

Expand|Select|Wrap|Line Numbers
  1. .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!

Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
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:

Expand|Select|Wrap|Line Numbers
  1. .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:

Expand|Select|Wrap|Line Numbers
  1. .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!
3 Weeks Ago #2

Abhean
P: 28
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. :)
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,761
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.
3 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,761
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.
3 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.