INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How should I return records

How should I return records

(OP)
I have a bound form with the following comboboxes

CODE

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.

CODE

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.

RE: How should I return records

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.

RE: How should I return records

(OP)
That worked thanks

RE: How should I return records

(OP)
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)

CODE

Private Sub cmdSearch_Click()
    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
               
         
         frmEdit.RecordSource = "select * from tbldata where " & GCriteria  '424 object required here 
         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

RE: How should I return records

'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

RE: How should I return records

(OP)
that worked perfect! Thank You

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close