Store Dlookup Value in table

[hardhitter06]

Hello All,

Access 2003.

Edit: These fields are on my input form.

I have a field called "GeneratedItemNumber" in my table "tblMain". This field uses a number field "MyNumber" and a category field "Category" and combines these values using this code in the generateditemnumber control source:

=DLookUp("CategoryAbbr","tblcategory","category='"+[category]+"'") & [mynumber]

Works great except this value isn't store in my "tblMain" for obvious reasons because the control source isn't "generateditemnumber". I was wondering what steps I need to complete in order for this value to be stored in my table??

In other words, I will need to query by this item number down the road and if it isn't being stored (in tblMain in particular), I won't be able to locate these records by their item numbers...

Thank you in advance,

Josh

 

[boblarson]

Ah, I'm disappointed. I would have thought you have been around here long enough to know that you do not store such things in the table since you have the associated category number already stored. That is a violation of normalization to store both. You just need to use a query to get the info when necessary.

 

[boblarson]

Or, if it isn't - then I'm misunderstanding and what the purpose of your generated number is. What is the primary key of the record?

 

[hardhitter06]

mynumber field

 

[boblarson]

mynumber field

It would appear, from your description, that number field is stored in the table. Which means you should be able to locate the information using a query later. I'm not seeing why you wouild need to store the concatenated values.

 

[hardhitter06]

I just don't understand how to search by generateditemnumber in a query when the field I'm looking for is concatenated by 2 fields (categoryabbr + my number)??

I.E the 5th item to be added to the database (a computer) would be cpu5 using the 2nd column of the category table along with the Dmax Mynumber field

 

[hardhitter06]

It would appear, from your description, that number field is stored in the table. Which means you should be able to locate the information using a query later. I'm not seeing why you wouild need to store the concatenated values.

Because I want to search by the item number and not a counter number...

Oh and also, I need to upload the item info to the web so im going to create a report that has all the data laid out in the appropriate format so i can just copy and paste the lines i need.

 

[hardhitter06]

I guess I could add another field to the table and form and just copy what is generated into this new field as a workable fix?

Or is there code that I can write for this to happen?

 

[hardhitter06]

i just put the lookup value in the beforeupdate of the category field on my input form. works like a charm.