Display String from cbo query in a different txtbox

[Zydeceltico]

Hi All -

I have a form that has a combobox (cboPart) that returns 5 columns.

When the user makes a selection in the combobo the values of each of the columns populate several different bound controls.

However, I have one unbound control (txtMill). When the form is first opened txtMill is empty. When a selection is made in cboPart, txtMill becomes populated from the query results of cboPart letting the user know which Mill the Part is made on.

The challenge I am facing is that the user needs to enter some data on this form, save and close it, and come back later to enter more data. The only thing is that since txtMill is unbound it is empty when the user comes back and it is important that it still has the Mill name in it.

I am storing the Mill_ID on the form in a bound hidden control. I thought about running some in-code SQL on Load using the Mill_ID as criteria to populate txtMill but wondering if there is an easier more elegant means to get the same result.

Ideas?

Thanks as always!!

Tim

 

[Minty]

The OnCurrent event fires when you change between records?

So simply requery the combo / set your unbound control in that event ?

 

[Zydeceltico]

The OnCurrent event fires when you change between records?

So simply requery the combo / set your unbound control in that event ?

Tried that. Problem is that the original query on the combobox takes a passed public variable as criteria the first time it is used.

The variable doesn't exist the second time the form is opened so the requery resulted in both the combo box and the unbound text box both being empty.

edit: I should have said the query takes a function as criteria the first time it is opened.

 

[Minty]

I'm guessing that's on a new record?

If so check if you are on a new record, if not then set the combo record source to pull all the values, so that it matches what's stored already ?

 

[Zydeceltico]

I'm guessing that's on a new record?

If so check if you are on a new record, if not then set the combo record source to pull all the values, so that it matches what's stored already ?

Yes- the first time the form is opened and a selection is made from the combo box the user is creating a new record.

The second time the form is opened - obviously - it is not a new record. I should have been clearer. Sorry.

How do I set the record source for the combo "to pull all the values" when I open the form the second time?

 

[Minty]

In the oncurrent event. Something like

If Not Me.NewRecord Then 

Me.Yourcombo.Rowsource = "Select all the records you need here" 

End If

 

[Zydeceltico]

In the oncurrent event. Something like

If Not Me.NewRecord Then 

Me.Yourcombo.Rowsource = "Select all the records you need here" 

End If

The answer ended up being DLOOKUP! Works a charm.

Good info here:

http://access.mvps.org/access/general/gen0018.htm

 

[Zydeceltico]

AFter more struggles - and for future purveyors of this post who might also be pulling their hair out when the tetxbox they just got to populate with DLOOKUP seems to freeze that value in place for eternity - - - - a morsel of my new found experience:

1) put your DLOOKUP on the control's (in my case a textbox) Control Source. I pasted mine into the Builder - leave off the equal sign at beginning. The builder will add one.

Next - and most critical to save hours of getting P.O.-ed and yelling at the cat: ----- On your form's Active event put this code:

Me.[yourControlsName].Requery

If you don't put that line of code in the form, you will have the same value locked in place in your textbox for all eternity. - - - and you run the risk of insanity (...oh wait ..... I may be projecting)

But with it in place: Mais Oui! Voila!

 

[essaytee]

I know I'm coming in late here but others have suggested checking for 'is it a new record' and that I agree with.

Somehow, I think you are overthinking the issue and making it unnecessarily complex or awkward.

You state that your ComboBox has five columns of data, and I assume one column is a bound column to your form recordset. Is not your ComboBox performing a form of DLookUp, and that is how you can populate your controls on your form (ComboBox AfterUpdate Event, Form OnCurrent Event) including the unbound text box "Mill Name". You already have a hidden control "Mill_ID", no problem with that, but within the ComboBox query, is Mill Name one of the columns, if so, use that.

If this is the setup, then there should be no need to perform direct DLookUp calls, simply check against, as others above have said, Me.NewRecord and display a blank Mill Name for a new record or look it up from the ComboBox.

 

[Zydeceltico]

If this is the setup, then there should be no need to perform direct DLookUp calls, simply check against, as others above have said, Me.NewRecord and display a blank Mill Name for a new record or look it up from the ComboBox.

That is basically the setup - but not the entire workflow.

The nature of this form is that it records a start and stop time that could be hours apart from each other - which means the form must be closed and returned to later.

The form is data entry/add new/ empty when it is first opened.

When the form is first opened a selection is made from the combo box that populates 5 controls. Also important to note that there is a criterion for the Rowsource of the combobbox that is the value of a passed variable from a different form the first time the form is opened.

Three of the controls are bound. Two are not. The two that are not are simply informational for the user - but vital - and are the controls in question.

The second time the form is opened it is opened from a different location than it was originally meaning the value of the variable that existed the first time the form was opened does not exist resulting in there being no value to run of any kind of background process to populate the two unbound textboxes that are still important to have.

I am open to any other suggestions for populating those boxes - I've tried a bunch of different things that haven't worked.

The DLookup seems to be working fine. I was concerned about performance as the db grows but realized that - in this instance - it is likely not a problem as the table of parts that the DLookup gets its value from is very tiny - 140 records. The second DLookup table is even smaller with 20 records. Neither one of those two tables is ever going to grow very much - - if at all.

But I would love to hear of another way of accomplishing this - given the workflow: Open New; Create an incomplete record; return later (without dynamic referenced variable value to populate unbound controls) and complete record (possibly multiple returns to same record to add more data).


Thanks!

Tim