0

I have a part number generation system with several types of number all abbreviated, PRT short for parts, RAW short for RAW material and so on.. the numbers need to be unique something like PRT001, PRT002 or RAW001, RAW002 ext. ext. For this we will only use the RAW example

A table is used to generate the numbers [tbl_Raw_Num]. The table has 2 fields one an automatic number [ID] the other field is for the actual number entered [EnteredRawNumber]

To get the next availble number and enter it to the part number creation page

Dim RAW As String
RAW = "RAW00" & DMax("[ID]", "tbl_Raw_Num") + 1
Me.SCS_Part_Number.Value = RAW

Which works okay…. to write the entered value back to the table

DoCmd.RunSQL "INSERT INTO tbl_Raw_Num ([EnteredRawNumber])" & "VALUES('RAW')"

Which appears to work in VBA.. the ‘RAW’ variable shows the correct format RAW001 or RAW002… But when I look at the table itself the numeric values are missing, it only has the text IE RAW in every row…. The 001 or 002 ext. is missing

Any suggestions what’s causing the numeric values to disappear?

sambob_628
  • 123
  • 8
  • That's because you're appending the text `RAW`. – Kostas K. Sep 30 '20 at 15:17
  • `VALUES('RAW')` inserts the literal string `'RAW'`, it doesn't do anything with that variable. If you want to include VBA variables in queries, you need to use parameters, and how to do that is described in the duplicate. – Erik A Sep 30 '20 at 15:18
  • `"VALUES('" & RAW & "')"` – Kostas K. Sep 30 '20 at 15:19
  • Hi, I have read the post regarding using parameters I kind of understand the point. however, some of it was way over my head. But I am trying to understand. I think the section that applies to my question is 'values from forms and reports as parameters' so I have rewritten as DoCmd.RunSQL "INSERT INTO tbl_Raw_Num (EnteredRawNumber) Select [Forms]![New Part]![SCS_Part_Number]" To try and use the number directly from the text field…. But this gives an empty field in the table so not recognising the text box value... – sambob_628 Sep 30 '20 at 17:35

0 Answers0