SQL Insert from VBA to Access Database

[Forgemaster66]

Hi. My first post on the forum and I hope someone can help with this problem. I have a table called 'Inventory'. It has 11 fields, two of them being 'Num' and 'Plant'. There are a number of 'Plants' and the corresponding number ranges within those plants are:

Plant Number
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
etc.

So for TBM for example, it may have four existing records, 1000, 1001, 1002, 1003

To insert the next record for TBM, I would have to know the next Number
value, which would be 1004. If I set all the values for the fields in the
table Inventory from a VBA form manulally, I can use the following code to Insert the record. (var1 corresponds to the 'Number' value from the form and var2 the 'Plant' value from the form).

appAccess.DoCmd.RunSQL _
"(insert into Inventory values ('" _
& var1 & "', '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"

However, what I want to do is create an Insert that will calculate the next
available Number based on the Plant value, in the case of TBM, 1004. I have
tried the following but it doesn't seem to work, although the nested select
works fine on its own. Any ideas.

appAccess.DoCmd.RunSQL _
"(insert into Inventory values ('( _
select max (Number) + 1 from Inventory where Plant = `" & var2 & "`)', '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"

All code is VBA 6.3 from excel and the database is Access 2003 SP2. If you need anymore code, let me know.

Many thanks in advance.

Martin.

[abolos]

Hi. My first post on the forum and I hope someone can help with this problem. I have a table called 'Inventory'. It has 11 fields, two of them being 'Num' and 'Plant'. There are a number of 'Plants' and the corresponding number ranges within those plants are:

Plant Number
TBM 1000 - 1099
ABC 1100 - 1199
AMS 1200 - 1299
BBR 1300 - 1399
etc.

So for TBM for example, it may have four existing records, 1000, 1001, 1002, 1003

To insert the next record for TBM, I would have to know the next Number
value, which would be 1004. If I set all the values for the fields in the
table Inventory from a VBA form manulally, I can use the following code to Insert the record. (var1 corresponds to the 'Number' value from the form and var2 the 'Plant' value from the form).

appAccess.DoCmd.RunSQL _
"(insert into Inventory values ('" _
& var1 & "', '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"

However, what I want to do is create an Insert that will calculate the next
available Number based on the Plant value, in the case of TBM, 1004. I have
tried the following but it doesn't seem to work, although the nested select
works fine on its own. Any ideas.

appAccess.DoCmd.RunSQL _
"(insert into Inventory values ('( _
select max (Number) + 1 from Inventory where Plant = `" & var2 & "`)', '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"

All code is VBA 6.3 from excel and the database is Access 2003 SP2. If you need anymore code, let me know.

Many thanks in advance.

Martin.

Why not try this:
Select MAX(NUM) where NUM= TBM
text0.value=MAX(NUM)
text0.value=NUM+1

[JKing]

Assuming your field Number is actually a number data type.
You could assign the incremented value to var1 before trying to insert.

var1 = DMax("[Number]", "Inventory", "[Plant] = '" & var2 & "'") + 1
 
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (" _
& var1 & ", '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"
 

Also I removed the single quotes from var1 because if it's a number value you don't need to use them. Unless the field Number is a text data type in which case the Dmax function won't work properly

[Forgemaster66]

Assuming your field Number is actually a number data type.
You could assign the incremented value to var1 before trying to insert.

var1 = DMax("[Number]", "Inventory", "[Plant] = '" & var2 & "'") + 1
 
appAccess.DoCmd.RunSQL _
"(insert into Inventory values (" _
& var1 & ", '" _
& var2 & "', '" _
& var3 & "', '" _
& var4 & "', '" _
& var5 & "', '" _
& var6 & "', '" _
& var7 & "', '" _
& var8 & "', '" _
& var9 & "', '" _
& var10 & "', '" _
& var11 & "'))"
 

Also I removed the single quotes from var1 because if it's a number value you don't need to use them. Unless the field Number is a text data type in which case the Dmax function won't work properly

Hi JKing

I like the idea of createing the var1 value before the original insert statement rather than the nested select. var1 was originally type string so I changed it to type Number. (Num is also type Number in the access database). When I try to run the insert, the VBA debugger stops at the

dim var1 as Number

with "Compile Error - User defined type not defined"

Any ideas?

Martin

[JKing]

Yeah just change it to the following:

Dim var1 As Integer
 

[Forgemaster66]

Yeah just change it to the following:

Dim var1 As Integer
 

Hi JKing

Sorry to be a pain here, but having changed var1 to Integer, the debugger stops at the following line (highlighting DMax)

var1 = DMax("[Num]", "Inventory", "[Plant] = '" & var2 & "'") + 1

with the following error.

"Compile Error - Sub or Function Not Defined.

Do I need to add in something from TOOLS/REFERENCES ?

Martin.

[Forgemaster66]

Hi

I got the code to work you gave me JKing. I just needed to include

Microsoft Access 11.0 Object Library

from Tool/References, and it works great.

Thanks for your help.

Martin.

[JKing]

You're welcome. Glad it worked out for you.

[Forgemaster66]

A further question, is there an equivalent command to DMax (shown below) to bring back string to var1

var1 = DMax("[Number]", "Inventory", "[Plant] = '" & var2 & "'") + 1

I hope there is.

Martin

[JKing]

Could you explain the problem a little more. Is [Number] a text data type? Or is your insert failing because var1 needs to be of text data type?

[JKing]

I'm assuming the problem is that [Number] is a text data type. For example you have the three numbers 10000,3000, 5 all stored in that field. The number 10000 is obviously the greatest but DMax would return 5 because it processes strings differently.

If this is the case try:

var1 = DMax("CInt([Number])", "Inventory", "[Plant] = '" & var2 & "'") + 1
 

[Forgemaster66]

Sorry JKing, I should have explained more. The code you originally sent me works fine and the insert problem is solved.

What I want to do now is another query to perform an update on the table Inventory in the database, based on the number set with var2. I would like to input var2 via a combobox and get the results back to multiple comboboxes and textboxes so the changes for the update can be made there.

For example, the table Inventory has ten fields, three of them being Number (Number), Plant (String) and Location (String). I can set var2 to be 1000 say and I want to query the database to bring back data for the other fields matching 1000, ie if Number=1000, maybe Plant='TBM' and Location='Mill', and set new variables to equal the results.Thus

varnum = DMax("[Number]", "Inventory", "[Number] = '" & var2 & "'")
varplant = VBACOMMAND("[Plant]", "Inventory", "[Number] = '" & var2 & "'")
varlocation = VBACOMMAND("[Location]", "Inventory", "[Number] = '" & var2 & "'")

Where VBACOMMAND is the missing command for the return of strings so that varplant would = TBM and varlocation would = Mill

Does this make sence. If not I will try and explain further.

Martin.

[JKing]

I think I understand.
Try this function:

varplant = Dlookup("[Plant]", "Inventory", "[Number] = '" & var2 & "'") 
 

[Forgemaster66]

JKing,

you have done it again. It works a treat. Thanks for getting me out of trouble again.

Regards

Martin.

[JKing]

Anytime Martin, glad I could help once again.