very new to access - button to search another form

[Angel1974]

Hi i'm trying to add a button on a form next to a lookup box (customers name) which when clicked will open the customers form and show the name selected.

i.e.

Form A; customers name is selected from dropdown box (populated via lookup), button clicked which opens Form B and displays the record associated with the data in the dropdown on Form A.

I'm very new to access so be a basic as you can be please.

Thank you and Happy New Year!

 

[missinglinq]

Paul, of this forum, has a very nice little tutorial on this subject:

http://www.baldyweb.com/wherecondition.htm

 

[Angel1974]

I came across this earlier today but it didn't make much sense to me. I have no idea how to use that information, i.e. where to put it and how to use it.

Sorry for my ignorance, can't be good at everything can we!

 

[ArtMiles]

If you post the database I will help you more but this should get you started.

Enter something like this into the OnClick event of the button.

DoCmd.OpenForm "FormB", , , "[Customer Name Field]='" & Forms!FormA![Customer Name Field] & "'"

Hope this helps.

Art

 

[Angel1974]

Many thank for the offer, the database is attached.

What I am trying to do is this;

In form 'Items', if the buyers name is filled in, place a button next to it on the form which when clicked will open form 'Buyers' and display the full details for the selected name.

This is for my collectables business so there is no rush but I would like to get it out of the way.

Thanks

 

[ArtMiles]

I couldn't open this file. However what you want is very simple to do, try this.

Open the form in design view.
In the View menu click the ToolBox.
Select the command button.
Point over the detail section of the form and click.
If the Control Wizard is on click the Cancel button.
In the View menu click Properties.
Click the All tab at the top of the Properties window.
Scroll down to On Click........
When the curser is in this box you will see an arrow down button and ...
Click on the ...
Select the Code Builder option.
Between the line that starts Private Sub and the End Sub line enter this.

DoCmd.OpenForm "FormB", , , "[Customer Name Field]='" & Me![Customer Name Field] & "'"

Replace FormB with the name of the form you would like to open.

Replace the first Customer Name Field with the name of the control on the FormB that has Customer Name.

Replace the second Customer Name Field with the name of the control on tha active form that has the Customer Name.

Hope this helps

Art

 

[Angel1974]

Many thanks for that but I can't get it to work and after a few different checks I've discovered it's when I use it on an autolookup field. Is there a way around this as it's opening the form but on a a single empty record with no data.

It does however work perfectly fine on normal fields just as you said. I have another question too, is there a way to enter an item number and have it automatically added to several tables so there is a 'placeholder' on the form? I know many will argue about empty data taking up space, but it's so I don't have to type the data every time and all fields will eventually be filled out.

Anyway, hope you're having a great new year.

 

[boblarson]

Yes, your problem is that you are using lookups at table level (not a good idea). You can use them at form level using combo boxes and list boxes but it is not a good idea to use them in your tables. See here for why:
The Evils of Lookup Fields at Table Level

 

[Angel1974]

Many thanks for pointing that out Bob, guess I'm re-doing some forms then

I have tried to drop a combo box in the form with the same results, is there a particular way this needs to be done? Do the field names need to be identical if I'm using the code;

DoCmd.OpenForm "Buyers", , , "[Buyers Name]='" & Me![Combo33] & "'"

Should I think about re-doing the form and removing the original Buyers Name from the Table and replace it with the combo box renamed to macth the other form?

All the best

 

[boblarson]

no, this should work fine IF your combo's bound column was the field that has the name and not ID in it. But, a different method to select the second field but still leave it bound to the ID is to use:

DoCmd.OpenForm "Buyers", , , "[Buyers Name]='" & Me.Combo33.Column(1) & "'"

Which refers to the second column since combos are zero-based in the column numbering (0 is column 1, 1 is column 2, 2 is column 3, etc).

 

[Angel1974]

Ahem, sorry to sound stupid but how do I bind the combo? And more importantly what do I need to bind it to?

Sorry for the total lack of Access knowhow, very new

UPDATE: All works fine now, my mistake! I didn't know I had a hidden column so my combo box was off by one. Thank you everybody for your great help and patience.

 

[boblarson]

Glad you figured it out.