drop down list fill

[aman909]

I have two tables, one called customers and the other called products. I would like to create a form called orders.

On here what i would like to do, is a drop down list for the customer ID. When the customer ID is selected the rest of the fields get filled in with the correct details. So their firstname, lastname, address etc get put into the fields.

How do i do this?

 

[Uncle Gizmo]

>>> the rest of the fields get filled in with the correct details <<<<

do you mean fields, As in the information is bound to a table, or do you just mean Unbound text boxes? (not linked to an underlying table)

 

[aman909]

The information is bound to a table

 

[Lori-Mai]

That's two of us

I am having the same difficulties as well.

I have a table for my clients, which includes all the information I need on them and a form for daily input and invoicing. I would like to be able to do a lookup by my customer number in my form and have certain fields included in the daily form.

So far the only way in which I am able to do this is with a lookup field, that includes all the information on one line. I would like to set this up so that each piece of information is on a new line,
ie. Customer #
Last Name
First Name

These field are already set up on the customer table.

Any suggestion?

 

[Angelflower]

On the Orders form you want to use a combo box that is build with an underlinging query from the two tables that has all the fields want to use on the form. In the code behind the combo box use code for after update similar to the following:

Private Sub ComboCustomerName_AfterUpdate()
'Updates Customer Information
Me!CustomerID.Value = Me!ComboCustomerName.Column(0)
Me!DE_addr1.Value = Me!ComboCustomerName.Column(1)
Me!DE_city.Value = Me!ComboCustomerName.Column(2)
Me!DE_zip.Value = Me!ComboCustomerName.Column(3)
Me!DE_state.Value = Me!ComboCustomerName.Column(4)
Me!provid.Value = Me!ComboCustomerName.Column(5)
End Sub

 

[Lori-Mai]

Hi and thanks Angelflower

Think I am getting somewhere on this, but yet not quite right

this is what I have in the code

Private Sub ComboCustomer_Number_AfterUpdate()
'Updates Customer Information
Me!CUSTOMER_NUMBER.Value = Me!ComboCustomer_Number.Column(0)
Me!LAST_NAME.Value = Me!ComboCustomer_Number.Column(1)
Me!FIRST_NAME.Value = Me!ComboCustomer_Number.Column(2)
Me!ADDRESS.Value = Me!ComboCustomer_Number.Column(3)
Me!ADDRESS_2.Value = Me!ComboCustomer_Number.Column(4)
Me!CITY.Value = Me!ComboCustomer_Number.Column(5)
Me!PROV.Value = Me!ComboCustomer_Number.Column(6)
Me!POSTAL_CODE.Value = Me!ComboCustomer_Number.Column(7)
Me!PHONE_NUMBER.Value = Me!ComboCustomer_Number.Column(8)
Me!provide.Value = Me!ComboCustomer_Number.Column(9)
End Sub

What is happening, although all the info is there for the picking below the combo box, only the customer number (which is the lookup field) is being applied to the combo box.

Any suggestions?

 

[Angelflower]

That is correct. The combo box will only hold the field that you have set as the Bound column in the properties box. In the properties of the combo box I usually hide all the extra data from showing in the combo box by setting the column widths to: 0";2";0";0";0";0";0";0"

By setting the Me!CITY.Value = Me!ComboCustomer_Number.Column(5) on the form the field of City will hold the value of the combo column 6 (This is becasue column numbering is starting with zero)

Hope that helps.

 

[Lori-Mai]

Boy Angelflower........you can't believe how much you are helping me to understand!!!!

Ok, so maybe how I want to word this now is like so.

It is showing me all this information as I want. I know the customer number, and the remainder of the customer info. The only part that shows up in the combo box is the customer number.

What I would like to see is "rows" (as I would term it) with all the customer information, such as you would see when you receive an invoice from a retailer. Does that make sense and is it possible.

 

[ansentry]

aman909,

Have a look at my sample, this is the easy way to do it. The 2 tables are just linked by a key.

I also have a sample that will create a new invoice and a subform of orderitems, let me know if you want a copy.

 

[Lori-Mai]

ansentry

would this be the same in which I should use?

I am trying to create a form for "daily input" (which I would like to use in turn as an invoice) within the daily input, I want to be able to bring information from a "client table" into the daily form, which would do a look up by client number, and in turn, fill in some "personal" information from the client table

As well, I would be interested in see the sample that will create a new invoice, if you cre to share.

Thanks

Lori

 

[ansentry]

Lori,

I have attached a copy of my "Invoice with saved prices" sample.

Before you try to understand how it works, may I suggest that you look at ComboTo FillInControls191106_Update.zip which I posted in this thread, as it may help you understrand how they both work.

would this be the same in which I should use?

I can't answer that as I have not seen your db.

The attached sample was designed when I was using a 15" LCB with 1024x768, so the forms may not display in the centre of the screen.

 

[Lori-Mai]

Thanks John

I will review both of these, as it look much like what I hope to achieve.

Lori