Sql or a better way

[Mark]

Hello all,
I have three fields in one record. I’m am wanting to update different table
with these three fields, but create a record for each one.

So would look like this

From this
Tb1
[Field1], [Field2], [Field 3]
Data1, Data2, Data3

To this
Tb2
[Field1]
Data1
Data2
Data3

I am trying this below, but get syntax errors on my sql statement. Do you
see anything wrong with it? Or is there a better easier way to do this?

Do Until vCnt > 3

Select Case vCnt
Case 1
vDimCk = rs!ELength
Case 2
vDimCk = rs!EHeight
Case 3
vDimCk = rs!EWidth
End Select

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "
DoCmd.RunSQL (vSql1)

vCnt = vCnt + 1
Loop

 

[Douglas J. Steele]

The syntax for a single-statement Insert is

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Try changing your statement from

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "

to

vSql1 = "insert into tbDimCk (DimCk) values(" & vdimCk & ")"

However, I question your design. Shouldn't you also be storing WHICH
dimension each number corresponds to? Shouldn't Tb2 have 2 columns: one for
Dimension, and one for the value? And since you have to do something
different for each loop, I don't see much point in the Do Until structure.

Why not simply:

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Length', " & rs!ELength & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Height', " & rs!EHeight & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Width', " & rs!EWidth & ")"
DoCmd.RunSQL (vSql1)

 

[Mark]

Here's what I am trying to do. We have three fields in one record that
represents Length, Height, and Width. Length will always be the largest and
Width the smallest. However the user all enter it in different. The front
end is from an outside source, so i am unable to check it at entry.
What I am doing is adding it to a table then sql it in Order by. This way I
can get the smallest to largest and reinsert it back in my table in the
correct order.
I wrote a lot of if statements at first, but ran into errors, so this looked
like another solution. If you know of a better way please let me know and
thanks for all the help on this and other questions I had.

The syntax for a single-statement Insert is

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Try changing your statement from

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "

to

vSql1 = "insert into tbDimCk (DimCk) values(" & vdimCk & ")"

However, I question your design. Shouldn't you also be storing WHICH
dimension each number corresponds to? Shouldn't Tb2 have 2 columns: one for
Dimension, and one for the value? And since you have to do something
different for each loop, I don't see much point in the Do Until structure.

Why not simply:

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Length', " & rs!ELength & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Height', " & rs!EHeight & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Width', " & rs!EWidth & ")"
DoCmd.RunSQL (vSql1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,
I have three fields in one record. I'm am wanting to update different
table
with these three fields, but create a record for each one.

So would look like this

From this
Tb1
[Field1], [Field2], [Field 3]
Data1, Data2, Data3

To this
Tb2
[Field1]
Data1
Data2
Data3

I am trying this below, but get syntax errors on my sql statement. Do you
see anything wrong with it? Or is there a better easier way to do this?

Do Until vCnt > 3

Select Case vCnt
Case 1
vDimCk = rs!ELength
Case 2
vDimCk = rs!EHeight
Case 3
vDimCk = rs!EWidth
End Select

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "
DoCmd.RunSQL (vSql1)

vCnt = vCnt + 1
Loop

 

[David C. Holley]

Actually, I don't believe that any of that is neccessary. If you have
three fields you could create a VBA procedure that runs on the
AfterUpdate event of each that first checks if all 3 have valid values.
Then if all three do, you could use the code to capture the values and
then determine which values should be placed into which box. The
following code very simply switches the values of two fields on a form

a = Me!formField1
b = Me!formField2

Me!formField1 = a
Me!formField2 = b

Here's what I am trying to do. We have three fields in one record that
represents Length, Height, and Width. Length will always be the largest and
Width the smallest. However the user all enter it in different. The front
end is from an outside source, so i am unable to check it at entry.
What I am doing is adding it to a table then sql it in Order by. This way I
can get the smallest to largest and reinsert it back in my table in the
correct order.
I wrote a lot of if statements at first, but ran into errors, so this looked
like another solution. If you know of a better way please let me know and
thanks for all the help on this and other questions I had.

:

The syntax for a single-statement Insert is

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Try changing your statement from

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "

to

vSql1 = "insert into tbDimCk (DimCk) values(" & vdimCk & ")"

However, I question your design. Shouldn't you also be storing WHICH
dimension each number corresponds to? Shouldn't Tb2 have 2 columns: one for
Dimension, and one for the value? And since you have to do something
different for each loop, I don't see much point in the Do Until structure.

Why not simply:

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Length', " & rs!ELength & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Height', " & rs!EHeight & ")"
DoCmd.RunSQL (vSql1)

vSql1 = "insert into tbDimCk (Dimension, DimCk) " & _
"values('Width', " & rs!EWidth & ")"
DoCmd.RunSQL (vSql1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello all,
I have three fields in one record. I'm am wanting to update different
table
with these three fields, but create a record for each one.

So would look like this

From this
Tb1
[Field1], [Field2], [Field 3]
Data1, Data2, Data3

To this
Tb2
[Field1]
Data1
Data2
Data3

I am trying this below, but get syntax errors on my sql statement. Do you
see anything wrong with it? Or is there a better easier way to do this?

Do Until vCnt > 3

Select Case vCnt
Case 1
vDimCk = rs!ELength
Case 2
vDimCk = rs!EHeight
Case 3
vDimCk = rs!EWidth
End Select

vSql1 = "insert into tbDimCk (DimCk) " & " vdimCk " & " "
DoCmd.RunSQL (vSql1)

vCnt = vCnt + 1
Loop