Solved Filter Username via Textbox MS Access

[Bean Machine]

I have been working on a database used by staff to record loans taken out by students. I have a combobox that has the usernames of the students. I would like to make it possible for staff members to locate a username by entering the name into a textbox. I want it to filter to an exact students username, for example if a students username is gregdan I wouldn't want someone to be able to filter for just greg. I have looked everywhere for a solution but I can't seem to find one. Here is what I have come up with:

Private Sub txt_Filter_KeyUp(KeyCode As Integer, Shift As Integer)
If Me.txt_Filter = Me.cbx_Username Then
Me.Form.Filter = "[Items_In_and_Out] = " & Me.txt_Filter
Me.FilterOn = True
Else
MsgBox "Error"
End If
End Sub

Any help would be absolutely amazing and thanks in advance.

(txt_Filter is the textbox name and cbx_Username is the name of the combobox I am trying to filter)

 

[Ranman256]

the form should show all records.
dont use key up, use AFTERUPDATE

Private Sub txt_Filter_AFTERUPDATE()
If isnull(Me.txt_Filter) then
  me.filterOn = false
else
  me.filter= "[Items_In_and_Out] like "'*" & Me.txt_Filter & "*'"
  me.filterOn = true
endif

 

[Bean Machine]

the form should show all records.
dont use key up, use AFTERUPDATE

Private Sub txt_Filter_AFTERUPDATE()
If isnull(Me.txt_Filter) then
  me.filterOn = false
else
  me.filter= "[Items_In_and_Out] like "'*" & Me.txt_Filter & "*'"
  me.filterOn = true
endif

There is a syntax error that says:

"Syntax error (missing operator) in query expression '[Items_In_and_Out] like'.

Not sure why this occurred, "Items In and Out" is the name of the form, is there perhaps a problem with that?

Any help is greatly appreciated and thanks!

 

[theDBguy]

Hi. Just a quick question for clarification, are you trying to filter the form or the combobox? Also, I would probably use the Change event.

 

[Bean Machine]

Actually, I'll ask a completely different question if that's okay. I have the Username combobox associated with a query called "Student Info". Would there be a way when I click on a students username in the combobox to bring up the information associated with that student (i.e. student email, student number, etc.)? This may be a better idea as opposed to a filter option. Let me know what you think. Thanks!

 

[theDBguy]

Actually, I'll ask a completely different question if that's okay. I have the Username combobox associated with a query called "Student Info". Would there be a way when I click on a students username in the combobox to bring up the information associated with that student (i.e. student email, student number, etc.)? This may be a better idea as opposed to a filter option. Let me know what you think. Thanks!

Sure. Try adding a new combobox using the wizard and select the third option. Let us know how it goes...

 

[Ranman256]

[in out] is supposed to be the field.

 

[Bean Machine]

Sure. Try adding a new combobox using the wizard and select the third option. Let us know how it goes...

I get a type mismatch error when using the third option of the combobox. Is there any way I can connect the Username combobox to the other comboboxes? For example, when I click a students name or type it in the combobox it pulls up the information from the query in the student number, student email and student name comboboxes? Would it potentially be better for student number, email and name to be textboxes instead? Let me know what you think! Thanks.

 

[theDBguy]

Sounds like you may be using a lookup field, which could cause problems like this. Are you able to post a sample copy of your db?

 

[Bean Machine]

Not using lookup field no, the Username field is however indexed, I can see what kind of sample I can cobble together quick that won't give away information for students. Thanks again!

 

[theDBguy]

Okay, thanks. We can confirm why you were getting a type mismatch error once we see your sample db.

 

[Bean Machine]

Okay, thanks. We can confirm why you were getting a type mismatch error once we see your sample db.

For whatever reason I can't seem to attach the database file, it says "extension not valid" or something to that degree, what can I do to post this?

 

[deletedT]

For whatever reason I can't seem to attach the database file, it says "extension not valid" or something to that degree, what can I do to post this?

You must zip the database and upload it.

You also may want to try @MajP 's FAYT class here. It works great for filtering combo boxes as you type.

 

[Bean Machine]

I changed the students data to protect their information, all usernames, emails and etc. are made up. Anyway here it is. Let me know what you can come up with. Thanks for all the help!

 

[theDBguy]

Hi. Thanks for posting the db. Which form did you want us to check?

 

[Bean Machine]

In the "Items In and Out" form I would like you to explain to me how I can go about pulling up a students information by simply clicking or typing their username into the combobox labelled "Username". Effectively I want the Username section to be what will bring up student email, student number, and the items being signed out. I don't know how possible this is but thank you for looking!

 

[deletedT]

Try this

 

[theDBguy]

See if this is what you mean...

Edit: Oops, too slow...

 

[deletedT]

See if this is what you mean...

Edit: Oops, too slow...

Sorry, I thought you're sleeping. Here it's 2:00 AM.


Edit: @theDBguy Did you upload the correct version?

 

[theDBguy]

Sorry, I thought you're sleeping. Here it's 2:00 AM.


Edit: @theDBguy Did you upload the correct version?

It's 9:00 AM here. I thought I posted the correct version. I'll have to check... Thanks!