SearchForRecord issue

[grahamvb]

Hello Access-Programmers,

I am passing the variables varEmployerID and varContactID into a form. The variables are coming into the form fine. The If/Then catches them perfectly. The combo boxes fill flawlessly. As I step through the statement below all seems OK. varComboContact passes to cboContact and cboContact.Column(0) reads "2" as it should.

When it hits DoCmd.SearchForRecord, no error is generated but the record with ContactID #1 displays on the form, even though I can watch and see that cboContact.Column(0) equals "2". I tried Val(cboContact.Column(0)) to no avail.

    If varEmployerID > 0 And varContactID = 0 Then
        cboEmployer = varEmployerID
        Me.Requery
        cboContact = cboContact.ItemData(0)
        DoCmd.SearchForRecord , "", acFirst, "[ContactID] = " & cboContact.Column(0)
    End If

Can anybody explain where I am going wrong?
Thank you for looking into this.

 

[missinglinq]

...the record with ContactID #1 displays on the form...

My guess is that the record with ContactID #1 is the first record in the recordset, and that's what acFirst

[B]DoCmd.SearchForRecord , "", [COLOR="Red"]acFirst[/COLOR], "[ContactID] = " & cboContact.Column(0)[/B]

is telling Access to do...go to the first record! It's not the same as FindFirst when running through a recordset.

You have to leave that parameter empty if you want to use the Where Clause, as you're attempting, here.

Linq ;0)>

 

[grahamvb]

Thanks for looking at this. I tried leaving acFirst out of the line and still get the record with ContactID #1, and yes it is the first record in the recordset.

I also found this: http://msdn.microsoft.com/en-us/library/office/ff836254.aspx in my search for a solution which seems to indicate that acFirst tells SearchForRecord where to start and which way to search.

I am stumped by this one.

Thank you again for trying to solve this.

 

[missinglinq]

As is often the case, the Access help is confusing. The next question is, is ContactID defined, in the underlying table, as a Number or as Text? Your syntax will only work for a Number. For Text it would have to be

DoCmd.SearchForRecord , "", acFirst, "[ContactID] = [COLOR="Red"]'" &[/COLOR] cboContact.Column(0) [COLOR="Red"]& "'"[/COLOR]

Linq ;0)>

 

[jdraw]

I don't understand the use of "" in the syntax.
I did find this example and, as linq says, the data type is key to proper syntax.

http://ss64.com/access/searchforrecord.html
Good luck.

 

[grahamvb]

The quotes "2" indicate that the field or combo box column is a string value. I tried converting the strings to a value making sure that both the record and the combo box column were the same. I also tried to convert them both to a String. Both string and value strategies result in the same issue.