How should I return records 1

[Lhuffst]

I have a bound form with the following comboboxes

DatabaseName       FormName                                     recordsource                                                                                                            
category           category  = SELECT TblCategory.Category, TblCategory.ID FROM TblCategory;
EnteredBy          EnteredBy = SELECT TUsers.UserAbbrv, TUsers.WSSCID, TUsers.UserName, TUsers.UserType FROM TUsers WHERE (((TUsers.UserType)="work"));     
DateAdded          DateAdded = has date pickcer  
SubmittedBy        SubmittedBy = SELECT TUsers.UserAbbrv, TUsers.WSSCID, TUsers.UserName, TUsers.UserType FROM TUsers WHERE (((TUsers.UserType)="work"));

The form works great when adding new records but if the supervisor needs to edit the data entered, they have to search for the correct record.

My thought was to let them select one of the combo boxes above and then return all records that match that criteria.
I tried it with Enteredby using code that I found on this site but it keeps giving me errors so I guess I'm not understanding it correctly.

Me.RecordsetClone.FindFirst "[EnteredBy] = '" & Me![cmbEnteredBy] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

It keeps telling me it doesn't recognize me![cmbEnteredby] value (in this case lhuffst)
What am I doingwrong and since there are multiple choices they can choose from, is this the best way?

I can envision them wanting to get all the records for one person on a specific date. Not sure if that is possible
Any guidance or suggestions are definitely appreciated.

 

[MajP]

Check the spelling. It sounds as if the name is wrong. Any chance cmboEnteredBy, cboEnteredBy, etc.?
easy check
dim enteredName as string
enteredName = me.cmbEnteredBy 'if this name is correct intellisence will tell you. That is why you do not use ! because no intellisense
msgbox enteredName
'rest of your code

However, as you explain this it sounds like you want to use the same controls for adding records as for searching for records. That will not work. The comboboxes for searching should be unbound. If you want to use the same form for searching and entering data then you may consider putting search controls in the header or footer.

 

[Lhuffst]

That worked thanks

 

[Lhuffst]

MajP
I added a search routine that has a combobox that lists the various fields. When I click the search button I get a runtime 424 object required on the recordset line.
this is what I have:
Form:CobSearchField: (combobox)
Value List with: Category, EnteredBy, DateAdded, SubmittedBy, DateGivenToClerk

txtSearchString: (textbox)
Enter a string to search for

cmdSearch: Command Button with the following code (this is where the problem exists)

[b][I]Private Sub cmdSearch_Click()[/b][/i]
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        Debug.Print GCriteria
        
        'Filter frmEdit based on search criteria
               
         
         [COLOR=#EF2929]frmEdit.RecordSource = "select * from tbldata where " & GCriteria  '424 object required here [/color]
         Form_FrmEdit.Caption = "Investigators (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
    
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

I verified that the database fields are correct .
GCriteria is a global variable.

the debug.print statement shows the correct values
[EnteredBy] LIKE '*lhuffst*'

I created a query simulating the above string and it worked fine both ways
select * from tbldata where enteredby like 'lhuffst'
and
select * from tbldata where enteredby like '*lhuffst*'
I don't understand what I'm missing
Thanks
lhuffst

 

[MajP]

'424 object required here
This means you are trying to use object properties but do not declared an object
If you have a form "frmEdit" you can not return that object simply by using its name. You have to get it from the forms collection
Bang Notation:
Forms!frmEdit.recordsource
Dot Notation:
Forms("frmEdit").recordsource

If you call it from that form's module you can use Me.
Me.recordsource

 

[Lhuffst]

that worked perfect! Thank You