repost-update field based on multiple selections fromother fields..

  • Thread starter Thread starter Guest
  • Start date Start date
Shawna

(how is this a "queries" question?)

Assuming your example is not your actual data...

If you have an object that has properties you describe with [opt.]table1 and
[opt.]table2 and ... [opt.]table16, it sounds like your table design might
benefit from more normalization. I can't be sure, though, given "colors"
and "shapes".

What I do notice is that you are creating a row with "Red Circle", when you
could be simply storing a pair of IDs (e.g., ColorID=B, ShapeID=1). Adding
the text is redundant and unnecessary, since you can query back against the
two tables to find the text values. And you don't want to store the string
"B1", either. Store as individual IDs, in separate fields.
 
Sorry if this was the wrong discussion group to post to. I thought what I am trying to do requires a query so....I am going to give more information and maybe that will help

I have 16 tables with different sections of a partnumber / description. (The separate tables are necessary because of info coming from a different system).

What I would like to do is allow the user to choose the description parts from these different tables and 'build' the part number and description (concatentated) into an Item Master table. I understand I could query this info but as I need to store other data against the part number once it's used i.e. qty on hand, etc. I figure it would be easier to do this with an item number rather then querying all the time to determine the item number. A lot of the Item Master will be built based on another import which uses the part number imported and all these 16 tables to 'build' the description. With that said there will be a need for the user to be able to add parts to the Item Master manually and their requirement is that the descriptions be chosen from the predifined tables (which in turn have a letter code to 'build' the part number)

i.e. of a couple of tables
Materia
Code Des
A Sch10
B Sch40 s
etc.

Siz
Code Des
01 1/
02 1/
03 1/
etc..

etc...

So on a form I have combo boxes where the user can choose the parts of the description from the tables

What I'd like to do is capture these 'parts' and concatenate them to one field ItemMaster.Description and use the assocaited codes to concatenate to ItemMaster.ItemNo This will then be used for PO's, ect..

So far, I haven't even been able to capture what the user has chosen on the form - I am just trying to start with 2 tables first.

SELECT tblPipeSize.DESC, tblPipeMaterial.PipeDes
FROM tblPipeSize, tblPipeMateria
WHERE (((tblPipeSize.DESC) Like Nz([Forms]![frmAddPipe]![Combo3])) AND ((tblPipeMaterial.PipeDesc) Like Nz([Forms]![frmAddPipe]![Combo6])))

It returns nothing - even though I do have items chosen in the combo boxes. ( I will eventually add more tables and change the Select into an UPDATE
Sorry for the length of the post but I thought more info might help

Any help with this is greatly appreciated, thanks in advance

Shawn
 
Dim SQLAddPip

SQLAddPipe = "INSERT INTO tblTempItemMaster ( ItemType, Size )" &
" VALUES (Me.Combo1 , Me.Combo3)


DoCmd.RunSQL SQLAddPip

No errors but when I look at the table, the values are empty. How do I capture

Shawna
 
If Itemtype and Size are numbers try:

SQLAddPipe = "INSERT INTO tblTempItemMaster ( ItemType, Size )" & _
" VALUES (" & Me.Combo1 & ", " & Me.Combo3 & ") "

If they are both text fields then surround the values from the comboboxes with quotes
SQLAddPipe = "INSERT INTO tblTempItemMaster ( ItemType, Size )" & _
" VALUES (" & Chr(34) & Me.Combo1 & Chr(34) & ", " & _
Chr(34) & Me.Combo3 & Chr(34) &") "
 
Back
Top