Dynamic search as subform+order form to auto populate (1 Viewer)

[born2gamble]

Hi...new to Access, but learning..

I have a small issue that I havent been able to figure out yet.

Its a transportation business, so one customer can have several different pickup locations.

I have 2 tables
Customer table
Order Table


Is it possible to have an orders Form, where a user can search for companyA in a subform (basically this http://www.access-programmers.co.uk/forums/showthread.php?t=188663), where all the locations are shown for that company, then click the appropriate location, and have that info auto populate on the appropriate text boxes ( Address/ City State etc)

If this is possible, how would I go about linking the search results to the appropriate fields?

Thanks in advance

 

[John Big Booty]

I'd suggest that a Cascading Combo Box set might be a little more appropriate for your situation.

 

[born2gamble]

didnt know this existed. thanks for the link, appreciate it!

 

[born2gamble]

I have made a sample database, just have one problem. How do I make it store the value I selected on Combo1(frmstafflocator) to "main" (tbl) (pickup name)

Need that in order to pull up reports properly.

Attached the sample database.

 

[John Big Booty]

Sorry I'm unable to open your sample. What version is it?

 

[vbaInet]

Sorry I'm unable to open your sample. What version is it?

Most likely 2010.

@born2gamble - Here's how you can save it in 2007 version:

http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx

 

[born2gamble]

It wouldnt let me convert it to 2007 format. Attached is a 97 and 2003 format. (yes , the previous attachment was a 2010 accdb )

 

[John Big Booty]

I'm not entirely sure I understand what you are trying to do with those forms.

You might want to read up on the topic of Data Normalisation, and then re-think your table structure. You may also get some inspiration from the free data models available here.

Also consider implementing a naming protocol for your DB objects, avoid spaces and other special characters and limit yourself to alpha and numeric characters and the underscore. Something along the lines of TBL_TableName, FRM_FormName, QRY_QueryName etc.

You could also check out the sample DB here for a more advanced Cascading Combo Box set.

 

[born2gamble]

I will read up on that when I get home, I just quickly worked with the sample db you linked me to. This is what I need.(I did a noob version of this I know).
Its a transportation business , so when i run a query to make a report(rate confirmation) how will I be able to pull this data (address), i dont understand where the data is stored.

So on the order form : since one company can have many different pickup addresses. I would have a "tblPickup" that I would want this advanced cascading combo box to get the info from.

Carrier
Customer
Pickup Address - where I would like to implement this advanced combo box set
pickup date
Destination
Destination Date
etc

 

[John Big Booty]

In the sample the data is Stored in TBL_Address.

Now if you are going to implement that in real DB you might have the following Tables;

TBL_ClientID
ClientID (PK)
ClientName
etc.

TBL_ClinetAdd
AddressID (PK)
ClientID (FK)
StreetName
PCodeID (FK)
etc.

TBL_PCode
PCodeID (PK)
PCode
SuburbName
etc.

TBL_Delivery
DeliveryID (PK)
AddressID (FK)
DelivveryDate
etc.

Now if you know the DeliveryID from there you can work back through your linked tables to know who your client was.

 

[born2gamble]

Ah, I see...thank you very much...makes sense to me now.

 

[born2gamble]

Okay having to go with this format for now since I am going to incorporate onto an already established database.So kind of need this quick fix for now.

On frmStaffLocator I choose the pickup name, and choose address2, it finds the corresponding PickupID. This is fine for me.

If I select the PickupID listbox (turns black), it stores that data onto tblMain.pickupid. But I would like it to automatically do that without me having to select it.

Note: I made the PickupId a listbox on frmStaffLocator, but if you can make it work with a combo box or text box, by all means please do.

 

[John Big Booty]

You really need to have a look at your table structure it makes no sense to me

 

[born2gamble]

argh..that bad huh....I will read some more and re-structure it so that its easier to understand.

As is its...

tblpickup is just an address table

ID-(PK)
Name
Address
Address2

tblMain is the order form where most of the info will be stored.
ID (PK)
Driver
Phone
PickupID - (FK)will relate this to tblPickup (ID)

 

[born2gamble]

Attached is an updated database structure or a picture of the relationships.
I read the database normalization wiki, but am still new to this.

Again, the reason for my layout is because one of my clients can have many different pickup address locations.

Thanks