Refresh default value for combo box

[dmunsch]

I am trying to create a text box that will populate (on click) the most recent value in a query. A small catch is that while the form is open, new data will be entered into the query so the query has to be refreshed (from what i've seen that should be "Requery") to get the newest data. Is there some type of code to be able to click this text box to refresh the query then populate the top value in that query?

I tried going around this by basing the text box value on a combo box value. Basically, on click, the text box would read the same value as the combo box. The default value for the combo box is the value I want to enter (top value in query). But due to the data being entered while the form is open the default value is no longer what I want. The form is being refreshed on a timer to refresh the query but, the default value doesn't become the top value in the query any longer. Is there a way to make the default value in the combo box refresh to populate the new top value while the form is open?

[June7]

New data will be entered into the query from where - other users? Is this a multi-user split db?

[dmunsch]

The same user will enter data into the query. It is a single user db.

[June7]

Do you really just want to carry forward into a new record the value just entered into a field? One method is to set the DefaultValue property of control in that control's BeforeUpdate event:

Me.controlname.DefaultValue = Me.controlname

[dmunsch]

I can enter in the data into the query first and then open the form to make it work. But I want to make it fool proof so if I open the form first then the correct data is being entered instead of old data. So, I want to have a new record open then input the data into a query and make it show up in a text box.
The form I'm using has a different control source than the query I want to pull from. I'm not quite sure how to use your suggestion without changing the current control source.

**I think my wording may be misleading in my original post. Let me try to explain a little better.
I am taking measurements from a microscope where the measurements and a time stamp are being exported to Excel. I have Access pulling the measurements and time stamp from excel and placing them in a table. I then use a query to filter out unused data. I want to be able to take the most recent measurement from that query and place it in a text box on a form. I am taking the measurements while the form is open. I though it would be simple enough to just have an on click even to refresh the query and place the most recent measurement in a text box. My trouble is that the query does not get updated and I get the most recent measurement before I opened the form.

[June7]

Forms have a RecordSource. ControlSource is a data control (textbox, combobox) property.

Don't really understand what you are trying to do.

If you want form to open to a new record that is easy. If you want this new record to have a field automatically populate with value from an existing (as in the last entered) record, that requires code. I use only VBA.

Does the table have an autonumber ID field?

In the form Current event maybe like:
If IsNull(Me!fieldname) Then
Me!fieldname = DLookup("fieldname","tablename","ID=" & DMax("ID","tablename"))
End If

[dmunsch]

The table does not have an autonumber ID. It does have a time stamp though. I edited my last post to hopefully explain better what I'm trying to do.

[June7]

Maybe:

Me!fieldname = DLookup("fieldname","query","datetime=#" & DMax("datetime","query") & "#")

Still not sure what you mean by 'query does not get updated'. Which query? The dataset pulling from Excel? How is this done? By table link? If so the link and query should update should be automatic.

[dmunsch]

The dataset is pulled from excel by a table link. I see now that the query is being updated. I am basing my text box on a combo box and I think what I meant by 'query does not get updated' is that the default value I have for the combo box does not get updated. It still displays the default value when the form is opened, even though the combo box has the updated query information.

[June7]

What do you mean by 'default value I have for the combobox'? A default value is applied only when creating a new record and this is set by the DefaultValue property.

If the combobox is bound to a field then what you see is actual data in record.

[dmunsch]

I have
['name of combo box'].[ItemData](0)
as my default value in my combo box. So it looks like the default value is only applied when creating a new record. After the query is updated, can you applied this default value again without creating a new record?

[June7]

No, the DefaultValue property applies only when creating new record. Otherwise, if combobox is bound to field, the field value will display. If field is Null then the box will appear empty because Null is the field value.

[dmunsch]

Ok, thanks for clearing that up for me. One last thing. Can you enter data from a query to a text box directly?

[June7]

Don't understand the question.

A query can be the RecordSource for a form. If it is an editable query, and textbox is bound to field of query, data entered/edited in the textbox will pass to the table through the query.

[dmunsch]

This is not an editable query. I think that having the query be the RecordSource to the form could lead to editing data which I do not want to happen. Anyway, the information you provided for my first question helped me with my problem. Thank you very much!