multiple combo box from the same table

[hansnyc]

OK I have form FOrders from table Torders
has orderID, refshipper. refconsignee, etc...
i have made combo boxes for the field refshipper and refconsignee
both refer to my table tcustomers.
My orders are made on a 3rd party basis
A shipper today can be a consignee tomorrow
Therefore I'd like to have them all drawn from my Table TCustomers.
Now it works fine as a form. I can select them from the same table.

Where I have an issue is when I want to print my order. I open a report from a query. That query called QOrders. If I want to print on the report with the address listed for both my shipper and my consignee from 2 different records on the table Tcustomers, it will return the same address because I'm asking the query to return the field "address" from TCustomers. Then If I make a txt field on the report and tells it to return the field address from the query, it doesnt know for which record.

Is there anyway to work around that?
My only option for now is to create a table called TConsignee with the same data as TCustomers. Only when I save new data on one table how can it save it also on the other. Can a table auto copy itself on to another evertytime it is updated?

Thanks for your help

 

[RuralGuy]

Have a look/see here. I hope that helps.

 

[hansnyc]

Hi,
I see what this does but it doesnt solve my problem
In the sense that once I have 2 different tables I will have to enter new data in both tables. Unless there is a way to auto copy the data to the other table. All My customers can be SHippers today and receiver (consignee) tomorrow. I can make a table called Customers and another Consignee with the same data, but when I update Tcustomers with a new entry, I'd like to see it in TConsignee. thanks

Have a look/see here. I hope that helps.

 

[rainman89]

do an update query that will add the customer info to the cosignee table. make sure the cosignee table has the same names as customer or it could get confusign

 

[RuralGuy]

Two copies of the same table in a query will allow them to be Customers as well as consignee's without using two tables.

 

[hansnyc]

Ok so I put instance of Customername in the query from the same table.
How does the query knows to display the customername form the first combox?

Two copies of the same table in a query will allow them to be Customers as well as consignee's without using two tables.

 

[hansnyc]

I meant 2 instances of Customername

 

[boblarson]

I'm attaching a sample to show how this would work. It's very simplified, but look at the form and then at the report (also the query). It has ONE table for both consignees and customers.

 

[RuralGuy]

Thanks for jumping in with an example Bob. For some reason the system did not send me a wake up email.

 

[hansnyc]

Here is my DBA

I'd like to solve these 2 issues

1) when I open my Quotations form I'd like a set of services to be selected by default, ex: Paint and Pencil. So the user only has to input the quote amount

2) when I click on the "new" button in the confirmation form, I enter a new customer it is not shown right away in the combo boxes list. Is there anyway to update my combobox list instantly?

Thanks for your much appreciated help

 

[boblarson]

For number 2)

In the CLOSE event of the Customer form, put:

    Forms!frmConfirmation.[LM Company].Requery
    Forms!frmConfirmation.Combo93.Requery

But, please name things with meaningful names. Change Combo93 to something like cboConsignee. And get rid of spaces in field names and object names, so change LM Company to something like cboShipper to designate what type of control it is and what it houses (AND without a space in the name so you don't have to remember when to use brackets and when you don't have to.

As for the defaults, that is a bit more problematic. I'm not sure I have the time to come up with the programming for that one.

 

[hansnyc]

Thank you so much for your help and the tip
I changed my combo box names.

If you could just give me on line exemple on how to have default set values in my subform on load it would be fantastic

Thank you so much

For number 2)

In the CLOSE event of the Customer form, put:

    Forms!frmConfirmation.[LM Company].Requery
    Forms!frmConfirmation.Combo93.Requery

But, please name things with meaningful names. Change Combo93 to something like cboConsignee. And get rid of spaces in field names and object names, so change LM Company to something like cboShipper to designate what type of control it is and what it houses (AND without a space in the name so you don't have to remember when to use brackets and when you don't have to.

As for the defaults, that is a bit more problematic. I'm not sure I have the time to come up with the programming for that one.

 

[boblarson]

If you could just give me on line exemple on how to have default set values in my subform on load it would be fantastic

The only way I can think of at the moment would be to use SQL inserts to create the subform records and then requery the subform to make them display.

 

[hansnyc]

How the hell do I do that?
Can it stay on access?

Is there anything on the onload fonction that can say something like "in subform select refproduct where product from tblproducts = 1" ?

The only way I can think of at the moment would be to use SQL inserts to create the subform records and then requery the subform to make them display.

 

[boblarson]

How the hell do I do that?
Can it stay on access?

Is there anything on the onload fonction that can say something like "in subform select refproduct where product from tblproducts = 1" ?

Yes, this can be done in Access. Do a search on Insert queries, and you might want to google as well.

 

[hansnyc]

Found a bunch of stuff not sure what is good
Any pointers?
Thanks a lot for your time btw

Yes, this can be done in Access. Do a search on Insert queries, and you might want to google as well.