Find Function within Access Form 1

[Mike555]

The database I am working on has 3 Forms in which users input information. There are 3 tables which store that information (one table for each form). An autoexec macro runs each time the db is opened. This macro opens an Access Form that contains 3 command buttons (one for each form), and at this point the user clicks the button for the form they wish to open.

The question....
I want to create a 'Search' command button on the autoexec form which will allow the user to search fields of the 3 other forms. This search function should work just as the regular Edit, Find function does, only I want it to appear as a command button so that the user sees it upfront. (Most of my users wouldn't know how to go to Edit, Find to find something). However, I have absolutely no idea how to do this.

Appreciate any help....Thanks.

 

[WildHare]

Well, Mike, somebody's gonna have to do some work here. Either YOU will have to code a fairly complex routine that uses the FindRecord method against EVERY field in EACH table, or your User's are going to have to decide WHICH field in WHICH table they want to search through..

Either way, though, the FindRecord method is probably the one you want.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mike555]

OK...What if I want to shorten this search as follows:

The command button will search only one table. This command button should prompt the user for the unique key of the record for which the user wishes to find.

I've attempted to build the expression below but it doesn't work. RequestForm is the name of the form that is being searched.

=[ID]=[Forms]![RequestForm]

Thanks for your help.

 

[WildHare]

Tell ya the truth, rather than use a text box where you have to type in a value, why not build a combo box with the current values, and let them pick it? It's generally much faster, doesn't require typing (which leads to keystroke errors) and makes your form look more polished.

Just use the combo box wizard and select the third type, "Find a record on my form that matches..." or whatever it is. Set the combo box "AutoExpand" property to YES, then when the user starts typing, it will automatically jump to the first record that matches the characters as s/he types.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mike555]

That is a very good idea. Which leads me to another question... after the value is retrieved from the combo box can that record be opened automatically?

Mike

 

[WildHare]

THe wiz will stick the code in the combo's after update event to find and position to that record, after which, if everything else is hunky-dory, you can edit it to your heart's content.

Just try it....

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mike555]

OK...Here's what I've tried. It opens the correct form, but I don't know how to have the code look at the combo box (named goto1) and then find that value in the records.

Private Sub goto1_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_goto1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RequestForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_goto1_Click:
Exit Sub

Err_goto1_Click:
MsgBox Err.Description
Resume Exit_goto1_Click

End Sub

 

[WildHare]

Ah - ok, you're using the combo to OPEN another form, positioned to that record. A bit different than what I envisioned. I pictured a combo in the header section or something, that just positioned the current form...either way.

Try this:

Private Sub goto1_AFTERUpdate
Dim stLinkCriteria as string

stLinkCriteria = "{other form's KEY field}" = " & me!Goto1

DoCmd.OpenForm "RequestForm" , , , stLinkCriteria

End Sub


For example, here's a line from a button I just made:

stLinkCriteria = "[EmpNo]= '" & Me![cboPickEmpNo] & "'"


The choice in a combo is known AFTER the pick is made, so you most always use the AFTER UPDATE event to process it.

The BOUND column of your combo SHOULD be the KEY to the table that the other form is showing.

If you set it up right, it should work just fine. But it's easier to use in the same form, so you don't have to worry about passing a parameter to another form.


Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mike555]

Here is what I have...the code takes you to the proper form, but not the proper record. The record that appears is completely blank, and the message record 1 of 1 (filtered) appears at the bottom.


Private Sub goto1_AfterUpdate()
On Error GoTo Err_goto1_Click

Dim stLinkCriteria As String

stLinkCriteria = "[ID]" = " & Me![Goto1]"
DoCmd.OpenForm "RequestForm", , , stLinkCriteria

Exit_goto1_Click:
Exit Sub

Err_goto1_Click:
MsgBox Err.Description
Resume Exit_goto1_Click

End Sub

 

[WildHare]

Is your ID field a TEXT field? If so, you need to embed single quotes within the quoted "stLinkCriteria" string, being careful NOT to include any extra spaces. If you check my previous response, it shows them -

stLinkCriteria = "[EmpNo]= '[/red]" & Me![cboPickEmpNo] & "'[/red]"

I think this is your final hurdle...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Mike555]

Jim...My ID field is an Autonumber field. Does this make a difference in the code?


It looks as though there is ONE last hurdle...I've updated the code but a message appears stating "The Open Form action was canceled."


Dim stLinkCriteria As String

stLinkCriteria = "[ID]= '" & Me![goto1] & "'"
DoCmd.OpenForm "RequestForm", , , stLinkCriteria

 

[Mike555]

Nevermind...I just had to remove the single '' quotes.

Thanks for your help!!!