Access form/query help
I am not very good with access and need help with a form/query. Here is what I have
Data_table
Field1
Field2
Lookup_Num
Lookup_table
Lookup_Name
Lookup_Num
Lookup_table contains some names that are related to numbers. Data_table will store the Lookup_Num rather then the Lookup_Name.
In my form I display Data_table.Field1 Data_table.Field2 Lookup_table.Lookup_Name (as a combo box).
Access shows me all the name listed in Lookup_table.Lookup_Name like it should. How do I get it to convert the name to its number value and store that in the Data_table.Lookup_num field???
BTW this is Access 2007.
Thank,
Louis
Data_table
Field1
Field2
Lookup_Num
Lookup_table
Lookup_Name
Lookup_Num
Lookup_table contains some names that are related to numbers. Data_table will store the Lookup_Num rather then the Lookup_Name.
In my form I display Data_table.Field1 Data_table.Field2 Lookup_table.Lookup_Name (as a combo box).
Access shows me all the name listed in Lookup_table.Lookup_Name like it should. How do I get it to convert the name to its number value and store that in the Data_table.Lookup_num field???
BTW this is Access 2007.
Thank,
Louis
ASKER
Ok. So an even dumber question. Where do I put "Data_table.Lookup_num = combobox.column(0)". Do I edit the form and go to the form query and put this in for the field name???
Louis
Louis
how do you save the information to the form?
That would be done if you were saving it using code ie you had a button with "Save" at the bottom so you could input your information but nothing would be updated until you hit that button.
If you arent then on the data tab of the combobox properties make sure that the bound column is the column that contains the numeric data - if it is the first column then it should be 1 etc
That would be done if you were saving it using code ie you had a button with "Save" at the bottom so you could input your information but nothing would be updated until you hit that button.
If you arent then on the data tab of the combobox properties make sure that the bound column is the column that contains the numeric data - if it is the first column then it should be 1 etc
ASKER
Yes I do not have a submit button.
Still not getting it... So let me try another way...
Here is what I have for the combo box property sheet under the DATA tab
Control source: Lookup_Name
Row Source: Lookup_table
Row source Type: table/query
Looking at Lookup_Table I have Lookup_Name as the first row and Lookup_num as the second. So that would mean that Bound to Column should be = 2???
Then below this Combo box I need to include another box for Lookup_Table.Lookup_num???
Still not getting it... So let me try another way...
Here is what I have for the combo box property sheet under the DATA tab
Control source: Lookup_Name
Row Source: Lookup_table
Row source Type: table/query
Looking at Lookup_Table I have Lookup_Name as the first row and Lookup_num as the second. So that would mean that Bound to Column should be = 2???
Then below this Combo box I need to include another box for Lookup_Table.Lookup_num???
Yes, if you want to store the number then you should have the bound column set to 2 as you have above.
Now, Instead of the control source being the lookup_table.Lookup_name it should be the Data_table.LookupNumber - as this is where you will be storing the actual data - the row source is correct. If you do this you shouldnt need the other hidden field as it is already reading the number from the combo box
Now, Instead of the control source being the lookup_table.Lookup_name it should be the Data_table.LookupNumber - as this is where you will be storing the actual data - the row source is correct. If you do this you shouldnt need the other hidden field as it is already reading the number from the combo box
You will also have to change the number of columns to 2, and make sure to set the widths correctly so that the number is hidden
ASKER
I did not get the last comment you said about change number of columns to 2 ....
But now when I try to add a record in the form and I select the combo box I see the list of name but when I try to select a name I get "Control Cant be edited. Its bound to the expression '[Data_table]![Lookup_num]
But now when I try to add a record in the form and I select the combo box I see the list of name but when I try to select a name I get "Control Cant be edited. Its bound to the expression '[Data_table]![Lookup_num]
If you want to display the name but store the number in the table then you set the combo box to have 2 columns - the first is the name as you want to display and the second is the number which you can hide by giving that column a width of 0.
Do you still have your txtbox that you are storing the number in? if you do get rid of it.
If your form bound to the table that you are trying to update? if it isnt it should be in this instance
Do you still have your txtbox that you are storing the number in? if you do get rid of it.
If your form bound to the table that you are trying to update? if it isnt it should be in this instance
ASKER
I finally figured out where this 2 columns thing was.. Now I can see my text and its value in the combo box pull down. I am still getting the "Control Cant be edited. Its bound to the expression '[Data_table]![Lookup_num]
I got rid of the txtbox for storing the number.
How do I bind the form to the table I am trying to update?
I got rid of the txtbox for storing the number.
How do I bind the form to the table I am trying to update?
ASKER
After doing some more reading I figured out that Access 2007 has a Combo Box Wizard. Using this wizard I was able to make the combo box work correctly.
Thanks,
Louis
Thanks,
Louis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hth
Joe