Create A Search Field on Form

[pbDudley]

Hello


I have one form created using a query. Then I dragged some tables onto the form, creating subforms, that are all tied together via one unique record.

I can't figure out how to add one text field and one button where a user can enter a specific plan number (is unique), then click the button to populate the form.

I'm assuming it must be something with:

On the button, go to on click in properties, enter the VB code, and enter the code referring to the value in the text box but I don't know how right this.

Any help is appreciated.

[Bob Fitz]

Hello
I have one form created using a query. Then I dragged some tables onto the form, creating subforms, that are all tied together via one unique record.

I can't figure out how to add one text field and one button where a user can enter a specific plan number (is unique), then click the button to populate the form.

I'm assuming it must be something with:

On the button, go to on click in properties, enter the VB code, and enter the code referring to the value in the text box but I don't know how right this.

Any help is appreciated.

Can you post a copy of the db

[pbDudley]

I don't think I can, it has too much proprietary data in it.
I do see this example:
MS Access search for record by textbox instead of dropdown - Stack Overflow

It doesn't involve a button, entering code into a text box, it looks fairly straight forward, but I'm getting an error I believe at the code that is red below (error states Compile error: user-defined type not defined. The only part I changed is the rs.FindFirst "[PLAN]=" & txtGoTo & """" I changed ID to PLAN
Private Sub txtGoTo_AfterUpdate()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.RecordSet
Set rs = Me.RecordsetClone
rs.FindFirst "[PLAN]=" & txtGoTo
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.RecordSet.Bookmark = rs.Bookmark
End If
rs.Close
txtGoTo = Null
End Sub

[Bob Fitz]

Hello
I have one form created using a query. Then I dragged some tables onto the form, creating subforms, that are all tied together via one unique record.

I can't figure out how to add one text field and one button where a user can enter a specific plan number (is unique), then click the button to populate the form.

I'm assuming it must be something with:

On the button, go to on click in properties, enter the VB code, and enter the code referring to the value in the text box but I don't know how right this.

Any help is appreciated.

Why not use the wizard to create a combo box that will find the record selected in that combo. The sub forms should update automatically.

[dblife]

If you go to the underlying query and make it ask for the value you want to search for.
Say your search field on the form (fMain) is called txtSearch
If the key field is called employeeID go to the underlying query and in the 'criteria' field for employeeID, ask it to look in txtSearch on the form fMain by typing =forms!fMain!txtSearch
As long as the form is based on the query, it should filter the form record you see.
If you need to change the value once the form is opened, add a 'refresh' button to requery the form fMain (you can google the code for that)

[Welshgasman]

http://allenbrowne.com/ser-62.html

[moke123]

rs.FindFirst "[PLAN]=" & txtGoTo

If Plan is a text datatype it needs to be delimited.

rs.FindFirst "[PLAN]=""" &  txtGoTo & """"

also

Me.RecordSet.Bookmark = rs.Bookmark

should be

Me.Bookmark = rs.Bookmark

[pbDudley]

Im still getting a compile error:

Compile Error:


User-defined type not defined

'Code to filter the form for PLANS
Private Sub txtGoTo_AfterUpdate()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[PLAN]=""" & txtGoTo & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
txtGoTo = Null
End Sub

It seems to not like this
Dim rs As DAO.Recordset

But I could be wrong on this.

I can try the macro method and adding the criiteria to the query as some others mentioned, I just thoght this would be an easy snippet of code for this, Ill keep testing though, thank you for the help

[pbDudley]

Or I'm trying to filter all records on a form and this code is only made for a text box. I can try the macro and query options

[Welshgasman]

What version of Access are you using?

[moke123]

Here's a simple example.

The first combobox filters by number datatype.

the 2nd combobox filters by text datatype

the textbox filters by number(autonumber)

[pbDudley]

Hello
I got the search code to work:
Private Sub txtGoTo_AfterUpdate()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[PLAN]=""" & txtGoTo & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
txtGoTo = Null
End Sub

I think the problem was I didnt have certain reference libraries checked in my VB. Another user mentioned this to me and it worked on another issue and now this code works. It fills out the form when I enter the PLAN number, than the whole form populates with the correct data.
Thank you all for the help