Get Price Type

[adam]

I’ve added a lookup column with the name Customer ID to the table “orders” where data get added as a new customer is added.

This add ups more text filed to my form create new invoice. Ive also added text boxes to the form where when the customer ID is selected from the combo box the corresponding text boxes to be filled from the customers query table.

Here is the code I’m using so far but I’m getting error messages with this

Code: Select all

Private Sub Customer_ID_AfterUpdate()
Me.Customer Name = [Customer_ID].Column(4)
Me.Address = [Customer_ID].Column(5)
Me.Age = [Customer_ID].Column(7)
Me.Sex = [Customer_ID].Column(8)
Me.Country = [Customer_ID].Column(6)
End Sub

What am i doing wrong in here?

[HansV]

The line

Me.Customer Name = [Customer_ID].Column(4)

is not valid because there is a space in the name of the control. You must enclose names that contain spaces in square brackets:

Me.[Customer Name] = [Customer_ID].Column(4)

(For this reason, I never use spaces in the names of fields, controls, tables, etc.)

[adam]

I guess still there seems to be a problem which is preventing from the text boxes to get populated with appropriate data.

I've attached the database.

[HansV]

I won't be able to look at this until later today - I'm on a computer with Access 2003 at the moment, so I can't open an .accdb database.

[adam]

I'll wait for the response then.

[JohnH]

There are two things wrong:

* The combo box only has one column so you can't retrieve data from the other columns. You need to add extra columns to the row source of the combo, and increase its number of columns.
* You don't need any code in the after update event, because you are not copying the values into fields in the orders table. All you are doing (correctly) is displaying them on the form. The existing control sources will do that without any code ( as long as the column numbers are correct.)

[adam]

Thanks fore the reply John. should the row source of the combo be as,

SELECT [Customers].[Customer ID], [Customers].[Customer Name], [Customers].[Address], [Customers].[ Age] [Customers].[Sex], [Customers].[Country] FROM Customers;

[HansV]

You can set the Row Source of the combo box to Customers, and you need to set the Column Count property to the number of fields.

[adam]

I've set the Row Source of the combo box to "Customers Query" table as it contains the column "Customer Name". And set the column count property to 9. With this situation all the fields get filled as required. But If I set the column count property as 1 only one filed gets field.

My intention for the combo box customer ID is to show only one column (the column containing the customer ID)but fill all the text boxes.

But this cannot be achieved in Access. Right?

[HansV]

Yes, it can. Set the Column Count property to 9, and use the Column Widths property to determine which columns are displayed and which are hidden. A column width of 0 hides a column.
If you only want to display the second column (assuming that the first column is the Customer ID, which you probably want to hide), set the Column Widths property to

0";1";0";0";0";0";0";0";0"

If you want to display the first and second columns, and hide the rest, use

1";1";0";0";0";0";0";0";0"

[adam]

Thank you Hans. It does hide the required columns now.

[adam]

I’m trying to populate the user ID text box in the “create new invoice” form with corresponding data from the column “Record C No” in employees table.

But my formulas aren’t working.

Which means; when I select the employee name from the combo box that is bound to the table “orders”, the texbox User ID will get populated with the appropriate Record C No from the employees table.

[HansV]

I don't see why you need the text box, but the expression =[lngEmpID].[column](2) refers to the THIRD column of the combo box, for the column index starts counting at 0: Column(0) is the first column, Column(1) is the second column, etc.
Since the combo box has two columns (Column Count = 2), there is no point in referring to the third column. If you want to refer to the second column, use =[lngEmpID].[column](1)

[adam]

Hans, I Still do not get the corresponding “Record C No” from the employees table when I select the employee name from the combo box that is bound to the table “orders”?

For example when I set the expression

=[lngEmpID].[column](2) to =[lngEmpID].[column](0) I get the primary key Number. But when I set the expression to =[lngEmpID].[column](10) which is the column where the “Record C No” resides, I don’t get any figure on the text box User ID?

My intention is to get the number 4754 when I select Adam as the Bill User.

[HansV]

The row source of the combo box is

SELECT [lngEmpID], [First Name] FROM tblEmployees ORDER BY [First Name];

This selects only 2 (TWO) fields from tblEmployees. Moreover, you have set the Column Count property of the combo box to 4. So it stands to reason that referring to Column(10) won't work.

[adam]

Thanks for the reply Hans. According to the suggestion I've changed the fields. Now the form does not show the Employee Name. The rest works fine.

What Have I done wrong now?

[HansV]

You should set the column width of the columns that you don't want to see to 0.

[adam]

Thankyou Hans. It works fine now.

[adam]

In my products table I've set the primary key as the code for the products. How could I change the number formats as 0001, 0002 and so on?

[HansV]

Set the Format property of the Product Code field in the table and of the text box bound to this field to the custom format 0000
You can't select this format from the dropdown list, but you can type it in yourself.