How to have a form field based on a query?

[rtcary]

I have a combo box on a form that gives the user an opportunity to select an Ad type with the primary key stored. Now I want to have an unbound field that uses a query based on the saved key to populate another field. When I right click on the unbound field, I do not see an option to use a query. No doubt I'm missing something.

Todd

[ranman256]

You can have the combo AFTER UPDATE event run a DLOOKUP to populate the unbound box

txtBox = Dlookup("[field]","[table]", "[lookupKey]=" & me.comboBox)

(its a lot more programming to do a right-click)

[rtcary]

I'll give that a try though the DLookup syntax will take some trial and error.

What I meant by my reference to right-click is when I was trying to find properties or methods for the unbound field while in design mode, I did not find a way to use a query for obtaining the contents for the unbound field.

Thank you,

Todd

[ranman256]

You normally dont. Fields should be bound on the form.
But if you want and unbound to fill in data from the combo, I pull the info from the combo box query. I give it extra fields in the query, tho the user cant see them. col.width = 0
Then when the user picks an item in the combo, the AFTERUPDATE event just copies the data over. Its faster than DLOOKUP.

afterupdate()
txtBox = cboBox.column(1)

[ssanfu]

For clarity:


Tables have fields.
Tables store data.
Forms/controls do not store data. Forms/controls are a method to view data.

Forms may or may not have a record source. A bound form has a record source, either a table, a saved query or a SQL string. An unbound form does not have a record source.

Forms have controls. A control can be bound or unbound. A bound control has a control source - a field in the record source of the form. An unbound control typically does not have a control source. But an unbound control can have an expression in the control source.


There is no such thing as an "unbound field".

[rtcary]

OK! This works:
Private Sub Combo18_AfterUpdate()
Text20 = Combo18.Column(2)
End Sub

However, the value (column(1) ) no longer shows in the combo field *after* update. I assume that needs to be added to the above. If so, what is the syntax?

Many thanks...

Note: I added BoundColumn = 1. That seemed to fix it. Apparently, I guessed correctly at the syntax.

Todd

[rtcary]

Thank you. Learning the Access nomenclature is taking time coming from Delphi.

Todd

[rtcary]

Many thanks for the help as I dive into Access. Using the following, it works:

Private Sub Combo18_AfterUpdate()
BoundColumn = 1
Text20 = Combo18.Column(2)
End Sub

I have a minor question:

* I cannot find how to change an Objects name e.g. Text20 to AdAmount.

Again, thank you...

Todd

[ssanfu]

Learning the Access nomenclature is taking time coming from Delphi.

dBaseIII, dBaseIV and Turbo Pascal for me.......

* I cannot find how to change an Objects name e.g. Text20 to AdAmount.

In design view, dbl click on the control to open the properties dialog box.
Click on the other tab.
The top property is "Name".....