Form Lookup

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hello, here is a challenge I hope someone can help me with. I am creating a form using 3 different tables. The first table is a customer information table, the second is a list of items the third is a table that will be used to store information entered in the form.
Here is my problem How do I use the first table to look up the customers information and autofill the rest of the address boxes- i think i can fiqure this part out) then use the item table to create a drop down list of items to choose from and then store all of this information into a new table?

Thanks!!!!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Thanks this helped. Now i Just need to fiqure out how to limit a drop down box to products that match based on the value filled out in one of these lookup fields.
Any suggestions?
Thanks again.
 
Upvote 0
You could try creating a queiry that performs these limitations and link the quiery to a combo box as recordsource.

This would work by:

Creating the limitations you required into a quiery.

Setting a new combo box or existing combo box's control source to your limitations quiery,

this would allow you to filter out data that is not related to your queiry

I would recomend a cascading combo box so that the dropdown will automatically show available related data, with the filtered out data, set by the limitations in your quiery.
 
Last edited:
Upvote 0
This sounds like the very thing that has taken me (off & on) and about 10 years to work out, finally with the help of this board. Here is the programming that I got to work for my inventory.

Private Sub Product_Category_AfterUpdate()
Dim sSQL As String
Select Case [Product_Category].Value
Case "1"
sSQL = BeadList.RowSource
Case "2"
sSQL = FindingList.RowSource
End Select
[Product_Type].RowSource = sSQL
[Product_Type].Requery

Note that I had to put both the BeadList and FindingList queries in the form footer so that I had a rowsource to reference.

Hope you can modify the above and get yours working faster than I did!
 
Upvote 0
Hi thank you. I tried bothe suggestions and i cant get it to work.
Somthing i was playing around with was having a seperate subform to look up this data,
ifs half working. What I did was make my source a drop down box. Once you select a source lets says SP then the second drop down would give you a list of products that only have the assigned source listed as SP. But here is my problem. I cant get teh secondary fields to work. The points and status fields should automatically be populated when a product is selected. I also can not seem to get the data to store in my redemptions table.
I tried to set thecontrol source but when i do that the drop down lists are no longer working.
 
Upvote 0
I gave up... thanks for trying to help. My work is paying for me to take an advanced access class so maybe that would help me.
In the meantime, I ended up creating three different forms one for each division the customer belongs to. Each form has its own query which limits the products and customers based on the division or source that they belong to. All data is still going to be saved in one main table so that I can create my queries, reports and labels. Hopefully this will work. (Crossed fingers) Thanks again for your help.
 
Upvote 0
Sorry to hear you are having problems, Good to hear you work are paying for your qualifications :) always good :P

Feel free to pester me with any questions you may have
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top