Query Based combo box populating to a table

[ethan.geerdes]

Hello everyone,
I'm trying to make a database for equipment and related documents for this I have created a table for the systems and created tables for the technical documents that I will be needing to input. I plan to use a form for the end users to submit the data easily to the related document tables. In the form, I have a query based combo box with a drop down of all the systems but when I click submit on the form, the data from the query based combo box doesn't populate to the related table for the documents. Can this be done or do I need to try and find a different approach? I am runinng Access 2003 also. Thanks for any feedback.

 

[MarkK]

Welcome to the forum

when I click submit on the form

It feels like we don't have all the info, because a form doesn't have a submit button, or method, or property, or anything called "submit" on it by default. And if "submit" is something you created, then you have to show us exactly how it works--or fails--for us to troubleshoot it.

But are you aware that you can "bind" a form to a table, and that data updates from the form to the table are completely automatic? Check out the RecordSource property of the form, which is the first property on the Data tab of the Form's property sheet in design view. Enter the name of a table or query. Then, for each control, set the ControlSource property (also the first property on the Data tab of the control's property sheet) to the name of a field in the RecordSource. After that, if you update data in a control, Access handles table updates immediately and automatically, without the need to "Submit" the update.

There are other possible issues in what you posted, but this might be a good place to start.

Hope this helps,

 

[ethan.geerdes]

I am aware of this. I know the form can be bound, I know that if you click the arrows at the bottom of the form, it will enter your data. Even before I created the button though, It was doing this which is why I know it's not the button. I have the form record source (which is what it's bound to if I'm correct) called advisory messages. The fields I have are basic stuff like the date the message came out, the system it came out for and whether or not some that works for me actually took care of it or not. I have the query based combo box pulling it's query from a different table called Systems. Obviously, it is a table with the system names, nothing fancy. The problem I have is that when I input a system name for the combo box and put in the rest of the information and click on the next arrow, it still doesn't input that data into the table.

 

[ethan.geerdes]

The problem I have is that when I input a system name for the combo box and put in the rest of the information and click on the next arrow, it still doesn't input that data into the table.

I should have said that when I select a system from the dropdown of the combox instead of input.

 

[MarkK]

A combo connects to data in two ways. First of all, it queries a table (generally) to create the list of options you can select from, but secondly, it can also be bound to a field in the RecordSource of your form. Check out the BoundColumn property of the combo, which defines what data in the control is saved to the table. Also check out the combo's ControlSource property, which defines what field the BoundColumn is saved to.

In populating the combo's list, the most common pattern is to use a query like . . .

SELECT SystemID, SystemName
FROM tblSystems
ORDER BY System

Then you need to set the ColumnCount property of the combo to 2. You also need to set the ColumnWidths property of the combo to hide the ID, and only show the name, so the ColumnsWidths property might be . . .

0";2"

. . . so the first column is hidden and the user only sees and selects the SystemName.
In this case too, you would set the BoundColumn to 1, so the control only saves (or reads) the SystemID to (from) the ControlSource.

Did you do all of these steps too? Keep me posted,

 

[ethan.geerdes]

So I just want to make sure I understand, the control source is where the query comes from and the record source is the data that it is going to be pointing to in the table?

 

[ethan.geerdes]

I set the control source to ADVISORY_MESSAGES![SYSTEM_NAME] and I set the Row source to SYSTEMS![SYSTEM_NAME]. The record source of the form is the ADVISORY_MESSAGES table.

 

[MarkK]

No.

The RecordSource is the property of a Form that determines where the form gets data from. This might be a table name, a query name or SQL text.

The ControlSource is the property of a control, including a ComboBox, that determines what field in a form's RecordSource the control is bound to.

The RowSource is the property of a ComboBox (and a ListBox) that determines what data is displayed by this list supported by these controls. So a ComboBox might display data in its list as defined by its RowSource and yet not be bound--via it's ControlSource--to the RecordSource of the form. That would be called an "unbound" combo. You would choose not to bind a combo to a ControlSource, for instance, if it functions as a search or navigation tool. It is not there, in that case, to alter data, but to allow selection for other purposes.

Does that make sense?

 

[MarkK]

No, there would never be a "!" character in a ControlSource or a RowSource. The ControlSource property on the datasheet will show a Combo that contains valid field names as defined by the RecordSource. Select from that list.

Hope this helps,

 

[ethan.geerdes]

Ok, so if I want the combo box to pull information from the Systems table, I need to set the Record source of the form to the Systems table and make then bind all the other fields to the fields in the Advisory_Messages table? I mean that would be the only spot where I'm pulling data from. Would I then bind everything else to their perspective sections in the table, since that will be where users input the data for the advisory message?

 

[ethan.geerdes]

So I am getting an error while playing around with it, the combo box states that it is bound to [Advisory_Messages]![System_Name]. I thought that was what I wanted though was to query the information from [Systems]![System_Name] and put the control source (what is to be edited) as [Advisory_Messages]![System_Name]

 

[ethan.geerdes]

ha, just saw the last post. Thanks to the refresh. So if there should never be a ! in the control source or row source, what should it look like then?

 

[ethan.geerdes]

I want to thank you for your help by the way, I know I may be a little slow to understand this but I've stepped outside my comfort zone on this to try and make things a little more efficient in the work place.

 

[MarkK]

So if there should never be a ! in the control source or row source, what should it look like then?

RowSource is the name of a table, name of a query, or SQL text. The RowSource defines the list that is displayed by a ComboBox or ListBox.

The ControlSource is the name of a field in a form's RecordSource that the control is bound to. This is the data in the table that is edited when you edit the control.

 

[MarkK]

I want to thank you for your help by the way,

You're welcome. Really, the biggest skill you need to learn this stuff, IMO, is stick-with-it-ness, which you seem to have. Keep at it!

 

[ethan.geerdes]

So I got it to work finally! Thank you. I got one more question though, I have a few more tables for like security updates and stuff with the same format as the Advisory_Messages. How can I view this information via the system? Would I need to set up a relationship between the systems table and all the other tables for this to work? I was looking into Data access pages and stuff, and I would like to get this to look kind of like a web page or something, or try to tie it into sharepoint or something.

 

[MarkK]

I don't understand your question . . .

How can I view this information via the system?

What information? What is "the system?"

I would stay away from data access pages. That never really worked out for Microsoft, and they are removed from later version of Access.

Getting data into web pages is considerably more difficult than using Access forms, IMO. To some degree it's a matter of what you know, but the Web is stateless, right, so every round-trip to the server you have to rebuild everything all the time. Nothing is preserved between page requests.

Anyway, glad you got it working, cheers,