Form Based on Query > Adding New Data / New Columns

[pbaldy]

Happy to help Rick! Not sure what you're asking. An autonumber field is certainly common as the primary key, but not mandatory. Why do you think you need it vs what exists now?

[kamathmanoj]

Hi Paul,

The existing table has one record that is manipulated for incoming and production as follows:

First entry when the material for the product is received - all fields are populated.
Second & subsequent entries when the material is checked out for the production, employee ID and number of products made, weight of material remaining is recorded. (These fields are blank when material is received in the first entry.)

(Now if it is a partial use of the material from the first check out, the material is checked back in for subsequent use and the cycle repeats until the order is complete or the material is exhausted)

So several ins and outs of the material form only one entry but the table should ideally record each transaction separately, isnt it? Else we loose track of what happened in each check-out. Please let me know if I have to rephrase anything because I typed stupidly.

Regards,
Rick

[pbaldy]

I won't pretend to understand your business, but as a rule yes, I would record each transaction separately.

[kamathmanoj]

Hey Paul,

No worries - you know so much that we are in agreement on this anyways. I have let the CIO know that we will need to restructure the table and that may alter all its dependencies a bit. Turns out that he agrees its a long term project.

Here is a different question on the same type of form. I am trying to take the data from several fields on the form and concatenate them into one field so that I can generate a data matrix code. Do you think it can be done?

If yes, can you let me know how to have the code field pick all the values I need from the form into its own unbound field?

Example:
Field 1 - Supplier Name
Field 2 - Coil Tag Number
Field 3 - Width
Field 4 - Thickness
Field 5 - Part Number
Field 6 - Theoretical Yield
Field 7 - Supplier Name+Coil Tag Number+Width+Thickness+Part Number+Theoretical Yield (To be able to generate a data matrix code.

Regards,
Rick

[pbaldy]

In a query you can concatenate them. In design view:

DataMatrix: Field1 & Field2 & Field3

You could also try using a calculated field in the table, though I haven't used one myself.

[kamathmanoj]

Hi Paul,

That worked. My next question is a little more complicated.

I have a different select query that is feeding a different form.

I need to scan a bar code that fills in a text box for product ID with numbers. How do I check if the scanned code (in number format) matches any of the product ID's in the Product ID field of the query. I know it would be easier if it was a table - then I could use DLOOKUP but it doesnt want to work with a query. Can you help?

Regards,
Rick

[pbaldy]

DLookup() should work with a query; are you getting an error or something? It would have to be a saved query, not SQL. The alternative is opening a recordset on the query.

[kamathmanoj]

aha! so I have to create an object to store the contents of the query in order to be able to use dlookup()

[pbaldy]

Well, DLookup() won't work like DLookup("FieldName", "SELECT...", "Criteria"). The table/query argument has to be a table or saved query.