Different Table Control Source

[beardo]

Hi,

I am having problems bringing in data from another table in a form. I currently have 4 tables that are relevant. CUSTOMERS, ORDERS, ORDERS_MACHINES and MACHINES (The Orders_Machines table is used to break down the many to many relationship and includes a list of machines and the quantity purchased etc.)

I have made a form (Orders) which includes the customer ID and all of the information about the order. Such as date, total cost etc. I have then included a subform called Orders_Machines that includes all of the machines and how many were purchases.

In the MACHINES table, there is a field called 'New?' which deciphers if a the machine is new or not and will be used later in a make table query for warranty information. The warranty will expire in 6 months for 2nd hand machines and 1 year for new machines.

What I want to do, is bring the NEW? information from the MACHINES table dependant on what Machine ID is selected. But this doesn't work, do I have to use a control source? I can't get that to work either. Please help!

I can give more info if needed.

Thanks

 

[pbaldy]

I would use a combo box for the user to choose the machine, and include the "new" field in the rowsource. You can display the value with:

=ComboName.Column(x)

where x is the appropriate column (zero based).

 

[beardo]

Hi,

Thanks for your reply.

However I'm afraid I don't understand the second part of your reply? And when I put in the row source for the Machine_ID combo box it just brings up YES, NO, YES, NO etc (The values in New?)

Thanks

Richard

 

[pbaldy]

What is the rowsource? I would expect something like:

SELECT MachineID, Newfield
FROM TableName

The bound column should be 1, so the machine ID is saved, the column count should be 2, and the column widths something like

1"; 0"

 

[Banana]

What Paul is suggesting is that you just add one extra column to your old rowsource; you can do that by click the "..." button in the properties window and bringing up the query builder, then drag'n'drop that new field.

If your old rowsource worked correctly, it should still work correctly even with that extra column (e.g. you should see the machine name or ID) then in a textbox or where ever you want to dispaly "NEW", you set its Control Source = "<NameOfMachineComboBoxGoesHere>.Columns(x)

where x is the number of columns starting with 0. So if your query had three columns, and New column is the last, it would be 2 (e.g. third minus one= 2)

Did that help?

 

[beardo]

OK Yes it does now say:

SELECT [Machines].[Machine ID], [Machines].[New?] FROM Machines;

And I have set the other parameters as you said.

I get the correct values up but it is being pulled from the Machine_ID field from the 'Machines' table instead of the Orders_Machines table, is this correct?

Just for information, as I don't think I have noted. The New? field on the form is in the form of a checkbox.

Thanks

 

[pbaldy]

From the sound of it, I would expect the rowsource of the combo to come from Machines, but the control source bound to the appropriate field in the Orders_Machines table. Perhaps I've misunderstood your setup.

 

[beardo]

Ah no, pbaldy, what you said is correct. That is what is set in the settings of Machine_ID

Now it has done something. The first column on the form is bringing in machine_ID as it should. But now Quantity, is also being updates with Machines_ID instead of being empty and ready for user input, and the checkbox field still isn't being updated.

Rich

 

[beardo]

Ok the quantity problem sorted, stupidity on my account, but still can't get the New? to update when the machine_ID is updated?

 

[Banana]

You probably need to fire the calculated control in AfterUpdate event of the combobox?