Select query to open in form view

[Uschi via AccessMonster.com]

On my main form I have a command button for a select query (License Lookup).
When the user types in a license number in the Enter Parameter Value dialog
box and clicks OK, is is possible to have the query displayed in form view
instead of datasheet view?

 

[Joan Wild]

Add a textbox to your main form named txtLicenseNumber. Change the criteria in your query to refer to this textbox:
i.e. instead of [Enter License Number] put
Forms!NameOfForm!txtLicenseNumber
Substitute the name of your main form for NameOfForm in the above.

Next create a form with your query as the recordsource (the form wizard will walk you through it).

Change your command button on the main form to open this form instead of opening the select query.

 

[Uschi via AccessMonster.com]

Hi Joan,

Many thanks for your reply.

I tried your method and it worked beautifully. However, I would have to give
instructions to the users to enter a license number and then click the
command button. For the last five years the security people are used to
clicking on the License Lookup command button, entering the number in the
dialog box and seeing the result pop up in form view.

I am reconstructing this database to clean up alot of outdated tables. I have
not been able to figure out how the creator of the original database put in
that command button. I tried to do it using the form wizard but I get stuck
when it asks to join two matching fields. Joining the two fields will not
bring up the license unless the record with that license is already displayed
in the form.

I would be very happy to just start the whole process over to recreate that
License Lookup command button. Now to make matters worse, I have two Lookup
command buttons (Sticker Lookup and License Lookup) that I thought were
created the same way. I see now that they were not.

**The code for the Sticker Lookup command button is as follows:
Private Sub StickerLookup_Btn_Click()
On Error GoTo Err_StickerLookup_Btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sticker No Lookup Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_StickerLookup_Btn_Click:
Exit Sub

Err_StickerLookup_Btn_Click:
MsgBox Err.Description
Resume Exit_StickerLookup_Btn_Click

End Sub

**The License Lookup Command button is entirely different:

In the On Click property the License Lookup Btn brings up a macro to open the
form.

Can you help me?

 

[Joan Wild]

On the form where the StickerLookup_Btn is located, again add a textbox for the user to enter a Sticker number. A better option actually may be to use a combobox, to allow the user to select the sticker number (again the wizard can walk you through the creation of the combobox - have it lookup the stickerid in your table where the stickers are stored). You can then make a minor change in the code below. I'm assuming that sticker number is actually a number, and not text, I also named the combobox cboChooseSticker

**The code for the Sticker Lookup command button is as follows:

Private Sub StickerLookup_Btn_Click()
On Error GoTo Err_StickerLookup_Btn_Click

Dim stDocName As String
*** Dim stLinkCriteria As String*** change to
Dim lngLinkCriteria As Long

stDocName = "Sticker No Lookup Form"
lngLinkCriteria = Me!cboChooseSticker
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_StickerLookup_Btn_Click:
Exit Sub

Err_StickerLookup_Btn_Click:
MsgBox Err.Description
Resume Exit_StickerLookup_Btn_Click

End Sub

**The License Lookup Command button is entirely different:

In the On Click property the License Lookup Btn brings up a macro to open the
form.

Is that all the macro does? If so, you can use the code I posted previously, which you said worked for you
By the way, the code above for the StickerLookup could also have been done in a macro. You can use macros, however you have more flexibility using code, and you can handle errors gracefully.

 

[Uschi via AccessMonster.com]

Hi Joan,

I'm sorry. I looked back at my message and I see that I didn't make myself
clear. The code that I listed for you was the code that was on the old
database that I am now updating. I have taken tables out of that old database
and am now making an entirely new database.

What I want to do is recreate those Lookup buttons, or at least one of them
for the form. Both buttons bring up a dialog box and then the query comes up
in a form instead of datasheet view. The form gives me far more reflexability
in how that information is displayed for the Security Guards.

Also, I agree with your statement about using code. I think that is the way
to go. So, how in the world did that person make that Sticker Lookup button?

Your help is greatly appreciated.

Uschi

 

[Uschi via AccessMonster.com]

Hi Joan,

I'm sorry. I looked back at my message and I see that I didn't make myself
clear. The code that I listed for you was the code that was on the old
database that I am now updating. I have taken tables out of that old database
and am now making an entirely new database.

What I want to do is recreate those Lookup buttons, or at least one of them
for the form. Both buttons bring up a dialog box and then the query comes up
in a form instead of datasheet view. The form gives me far more reflexability
in how that information is displayed for the Security Guards.

Also, I agree with your statement about using code. I think that is the way
to go. So, how in the world did that person make that Sticker Lookup button?

Your help is greatly appreciated.

Uschi

 

[Joan Wild]

The code for the sticker lookup button was generated by a wizard.

If you open your form in design view and hit View, Toolbox to display the toolbox. With the 'control wizards' button enabled (it's the second button on the toolbox), click on the command button control and click on your form. Choosing Form Operations, and Open Form, and following through with the wizard's questions, you'll find that it generates the code you found.

In your case, since your form is based on a query, which has a parameter set for the criteria, you are getting a dialog box to enter the criteria, and then the form opens.

 

[Uschi via AccessMonster.com]

Hi Joan,

You did it, Joan! I just needed to look at it from a different perspective.

I used the command button on the toolbox (too many times to count now) and
followed the directions. My mistake was checking the box "Open form and find
specfic data" instead of "Open the form and show all Records".

I am encountering one problem however. In my form the text box for the name
shows #NAME? The rest of the information in the query is displayed.

Any ideas as to the problem?

Uschi

 

[Uschi via AccessMonster.com]

Hi Joan,

It's me again,

I deleted the form and started all over again. Whatever was wrong has
corrected itself - at least for now!

Many thanks for all of your help.