Finding a value

[Danryan]

I am having trouble finding the record I want on a form.

My form contains 2 combo boxes, a listbox, and 4 other text boxes. When the user selects a value from each of the combo boxes, a query is run to determine the listboxes contents. The listbox has 2 columns - one is hidden as it is the key field, the other is the boxnumber. The query is working fine and displays the correct result/s (ie the boxnumber), however I want to find the first record based on the code (hidden key field) as opposed to the boxnumber which it is currently doing. Can anybody please help me with this?

I have tried changing the bound column 2, and changing my code to:
rs.FindFirst "

 = '" & Me![List14] & "'"
It gave the following error- data type mismatch in criteria expression.

 

[dcx693]

You don't need to change the bound column for the listbox, unless you want to. Listboxes, like comboboxes, have a .Column property that you can use to refer to a specific column directly, whether or not it's the bound column.

If the code column is the 2nd column, try something like this:
rs.FindFirst "

 = '" & Me![List14].Column(1) & "'"[/FONT]

Realize also that if the [code] field is a numeric field, then you don't need to have the ' ' delimiters (as you would if it were a text field. In that case, you can just use:
[FONT=Courier New]rs.FindFirst "[code] =" & Me![List14].Column(1)[/FONT]

 

[vipersmind]

same same but different problem

I am trying to search a form using a value from another form.
[MaxFaceID] is the value from the inital form.
[frmEdit_EnterFace] being the form to search on and [FaceID] is the field to search in. so [FaceID] = [MaxFaceID].

I am using the code below but it gives me a 'Data Type mismatch' error.

I am sure that this is a simple thing for most of the gurus on this site , but is beyond me.......

Dim RS As Object
    Set RS = Forms!frmEdit_EnterFace.Form.RecordsetClone
    
    'Check if MaxOfFACEID field is blank.
    If IsNull([MaxOfFACEID]) Then
    MsgBox "There is no FaceID selected", vbExclamation
    Exit Sub  
    Else
    RS.FindFirst "[FaceID] = '" & Str(Me![MaxOfFACEID]) & "'"
    Forms!frmEdit_EnterFace.Form.Bookmark = RS.Bookmark
    Me.MaxOfFACEID.Value = Null
    End If

 

[pbaldy]

If FaceID is a number, then you don't want the single quotes (or the string conversion):

RS.FindFirst "[FaceID] = " & Me![MaxOfFACEID]

 

[vipersmind]

If FaceID is a number, then you don't want the single quotes (or the string conversion):

RS.FindFirst "[FaceID] = " & Me![MaxOfFACEID]

Hiya pbaldy

No FaceID is not a number it's Alpha Numeric and unique (PK).

 

[pbaldy]

Ah, perhaps it's the form reference. If that's not a subform, you don't need the "form" in the middle of it. Try these (I tried something similar in testing and it worked):

Set RS = Forms!frmEdit_EnterFace.RecordsetClone

Forms!frmEdit_EnterFace.Bookmark = RS.Bookmark

 

[vipersmind]

I tried as you suggested but same same......Type Mismatch!

The MaxFaceID value is derived from a query and displayed in a form with the cmd button Accept (which the RecordsetClone code is behind) and Close.

Would it make things easier if I searched the form directly from the select query and bypassed the first form that MaxFaceID value comes from?? Just an idea........

 

[Danryan]

Hi, I gave the original problem, and tried what you said.
As code is an autonumber - numeric
I tried the code below: (the other line didnt work)
rs.FindFirst "

 =" & Me![List1].Column(0)
At first there was an error messgae, but when I changed
the Column number to 0 that seemed to rectify it.
Now the problem is that it brings up the same record
every time I click on it, which happens to be the details for
the first record in the table (autonumber 1).
Could anyone please help?