auto update textbox depending on combo box 1

[srpatel]

Hello All,

I have two combo boxes on my form and about 10 text boxes. Combo box 1[color] has 6 lines e.g. Line1, Line2, Line3, etc. Each line has a uniqueID and each unique ID has an Asset Number.

I want a user to select the Line in cbo1 then able to select the uniqueID’s from cbo2 that is within that Line and then once that is done the Asset Number appears in a text box that is within that Line and for that Unique ID.

Cbo1 Cbo2 txtAssetNumber
Line1 UniqueID’s AssetNumber
Line2 UniqueID’s AssetNumber
Line3 UniqueID’s AssetNumber
Line4 UniqueID’s AssetNumber
Line5 UniqueID’s AssetNumber
Line6 UniqueID’s AssetNumber

There are several UniqueIDs. Each UniqueID has an AssetNumber. Currently all Asset Numbers are stored in a table called tblAssetNumbebers. I have tried having a combo box for assetNumber but cant seem to figure it out.

Any help would be great thanks.
Shreekant

 

[TheAceMan1]

How are ya srpatel . . .

Have a look at the [blue]DLookUp[/blue] aggregate function . . .

See Ya! . . . . . .

 

[srpatel]

Hey AceMan1 hows it going...?,

I have never used the DLookup aggregate function. Could you please give me an example?


Shreekant

 

[srpatel]

Hey AceMan,

I read up on the DLookup Aggregate function. this is how mine looks:

=DLookup("Asset_Number", "tblAsset_Numbers", "Asset_NumberID"=Forms!frmT4MAssets_B&S!Asset_NumberID")

and i get the following error:
The expression you entered has an invalid string
A string can be up to 2048 characters long, including opening and closing quotation marks.

Could you advice on what I have done wrong?

 

[PHV]

Depending of the type of Asset_NumberID (numeric or text):
=DLookup("Asset_Number", "tblAsset_Numbers", "Asset_NumberID"=Forms![frmT4MAssets_B&S]!Asset_NumberID)
or
=DLookup("Asset_Number", "tblAsset_Numbers", "Asset_NumberID[tt]'"=[/tt]Forms![frmT4MAssets_B&S]!Asset_NumberID) & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[srpatel]

Thanks for that PHV,

But I have just had an update from my clients and the text box needs to be a combo box now.

I have managed to work out the first two combo boxes by having this in the row source for cbo2. But i cant seem to have the the relevant asset number appear in cbo3 as all of them get listed.

Row source cbo2:

SELECT tblBS_UniqueId.[Unique Id], tblBS_UniqueId.ID
FROM tblBS_UniqueId
WHERE (((tblBS_UniqueId.LineID) Like [forms]![frmT4MAssets_B&S]![cboLineID]))
ORDER BY tblBS_UniqueId.[Unique Id];

row source cbo3:

SELECT tblAsset_Numbers.Asset_NumberID, tblAsset_Numbers.Asset_Number
FROM tblAsset_Numbers
ORDER BY tblAsset_Numbers.Asset_Number;

TblUniqueID
>ID
>UniqueId PK
>Asset_NumberID

tblAsset_Numbers
>Asset-NumberID Pk
>Asset_Number

Hope this is information can help. Appreciate all the help from you guys!

Thanks
Shreekant

 

[PHV]

Perhaps something like this as row source for cbo3:
SELECT N.Asset_NumberID, N.Asset_Number
FROM tblAsset_Numbers AS N INNER JOIN tblBS_UniqueId AS U ON N.Asset_NumberID = U.Asset_NumberID
WHERE U.[Unique Id] = [Forms]![frmT4MAssets_B&S]![cbo2]
ORDER BY 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[srpatel]

Hi PH,

That SQL query worked sweet!

One thing though is that when I change the unique ID, the AssetNumber field does not get updated.

e.g. I choose Line then Unique ID then I DO get the relevant asset Number, BUT whilst testing, I noticed that say in case a user made a mistake in choosing the wrong line and unique number, once he changes the asset Number combo box does not get updated!

Any ideas?

With regards to the above query, what does the Nfunction for?

 

[PHV]

what does the Nfunction for ?
N and U are aliases I used to shorten the SQL code.

combo box does not get updated
Perhaps a Requery in the appropriate event procedure ?
But I'm not sure to have really understood your form layout ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886