Populating a TextBox on a form dependant on another textbox using a query

[Richhol65]

I have a query that has two columns one being an Old Cost Centre and the other being a New Cost Centre

I have a form that has two ComboBoxes and then a TextBox that is populated by the concatenation of these two ComboBoxes this is the Old Cost Centre in the above query

I have an additional TextBox that I would like to be populated from the second column of the above query so it contains the New Cost Centre Column

Is this possible

 

[theDBguy]

One way is to use DLookup().

 

[bob fitz]

If the required data is in a column of the combo, then you could set the Control Source property to reference that column.

 

[Richhol65]

One way is to use DLookup().

Thank you - I have seen that but I can't work out where to put the DLookup (i.e. which textbox) so that the final textbox is updated could you advise me ?

So the two fields in the Query are called CCJN (Old Cost Centre) and FULLCC (New Cost Centre)

The TextBox that has the Concatenated Fields in it is called Text9 and this relates to the CCJN Field in the query and the Textbox that I want to be populated is called Text6 which I want to be populated by the FULLCC field in the query

 

[Richhol65]

If the required data is in a column of the combo, then you could set the Control Source property to reference that column.

Hi Bob

No the two combos are just for the users to select from two dropdown lists. I have then concatenated those in a TextBox which I wanted to use to help select the value for the second TextBox

I am sure I have done something similar previously but have not used Access for a couple of yuears

Thanks

Rich

 

[bob fitz]

Perhaps something like:
Dlookup("[New Cost Centre]","[NameOfQuery]","[Old Cost Centre] = " & [NameOfTextboxWithOldCentreName])
as the Control Source of the textbox.

 

[Richhol65]

Perhaps something like:
Dlookup("[New Cost Centre]","[NameOfQuery]","[Old Cost Centre] = " & [NameOfTextboxWithOldCentreName])
as the Control Source of the textbox.

Hi Bob

Thank you so much that has worked perfectly - I had the syntax incorrect

Thanks

Rich

 

[Richhol65]

Perhaps something like:
Dlookup("[New Cost Centre]","[NameOfQuery]","[Old Cost Centre] = " & [NameOfTextboxWithOldCentreName])
as the Control Source of the textbox.

Hi Bob

Slight strange thing when I open the Form and all fields are blank then I populate the two user select ones the final TextBox does not populate - when I change to Design View and then back to form view the TextBox is populated - am I missing something ?

Thanks

Rich

 

[bob fitz]

Hi Bob

Slight strange thing when I open the Form and all fields are blank then I populate the two user select ones the final TextBox does not populate - when I change to Design View and then back to form view the TextBox is populated - am I missing something ?

Thanks

Rich

Try:
Me.[NameOfTextbox].Requery
in the After Update event of the combo boxes.

 

[Gasman]

TBH I would have thought you would need to surround the form control value with single quotes?

 

[bob fitz]

TBH I would have thought you would need to surround the form control value with single quotes?

Actually, after looking at it again, I think you may well be correct. As you usually are

 

[Gasman]

Hi Bob

No the two combos are just for the users to select from two dropdown lists. I have then concatenated those in a TextBox which I wanted to use to help select the value for the second TextBox

I am sure I have done something similar previously but have not used Access for a couple of yuears

Thanks

Rich

Well if you concatenate them, you.must have both, so I do not understand the issue?
One control is a&b and the other is just b?