Append multiple rows using DMax

M

mdbGal

Hello All,

I think this can be done but I'm not sure. I want to be able to transfer
multiple lifts to another table and assign them a new primary key at the same
time, it's not working. I know I am missing something or maybe it really
can't be done this way? I THINK it has to do with the queries that use
transactions. Below is the code I am playing with but can't make it work.
There are lot of lines not in use but I fgured I should post all of the left
that I was playing with.

Dim Msg, Style, Title, Response, MyString
Dim NewMaterialID As Long
Dim rst As Recordset
Dim FileCount As Integer
Dim Counter As Integer
Dim strSQL As String

Set rst = CurrentDb().OpenRecordset("tblMaterialReceiving")

FileCount = DCount("lCoilID", "tblLifts", "[lCoilID]=" &
Me![txtCoilID])
Counter = 0
NewMaterialID = DMax("mrMaterialID", "tblMaterialReceiving") + 1

If Me.chkTransfer = -1 Then
Msg = "You are about to transfer this lift, is that what you
want to do?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Transfer Lift"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"

With rst
Do Until Counter = FileCount
NewMaterialID = DMax("mrMaterialID", "tblMaterialReceiving")
+ 1
.AddNew
'!mrMaterialID = NewMaterialID
'!mrCoilID =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCoilID]
'!mrReceivedDate = Me.txtTransferDate
'!mrInventoryTransferDate = Date
'!mrTransfer = True
'!mrTransferFrom =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCustomerID]
'!mrCustomerID = Me.cboTransferTo
'!mrNonConformanceID = Me.txtNonConformanceID
'!mrAlloyID
'!mrTemperID
'!mrGauge
'!mrWidth
'!mrReceivingNumber
'!mrQuantity =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtPieces]
'!mrNetWeight =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCalculateNet]
'!mrGrossWeight =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtGrossWeight]

.Update
'DoCmd.SetWarnings False
'DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID, mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT
DMax('mrMaterialID','tblMaterialReceiving')+1, 'S' & [crCoilReceivingNumber],
tblLifts.lTransferDate, tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"

DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID, mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT " & NewMaterialID & ", 'S' &
[crCoilReceivingNumber], tblLifts.lTransferDate, tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"
.Update
rst.MoveNext 'put in
'rst.MoveFirst 'moved up
DoCmd.RunCommand acCmdSaveRecord
Me.txtStatusID = 7
MsgBox "Be sure to tell the Receiving Department to tag the
Lift with Material Identification number " & NewMaterialID & "!", vbOKOnly,
"Material Receipt"
'DoCmd.SetWarnings True
Counter = Counter + 1
'rst.MoveNext 'put back seems to work best here?
Loop
.Close
End With
'DoCmd.OpenQuery "qupdTransferLifts"
'DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID, mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT " & NewMaterialID & ", 'S' &
[crCoilReceivingNumber], tblLifts.lTransferDate, tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"
Else
MyString = "No"
DoCmd.CancelEvent
End If
End If

Thanks to all who reply
 
G

Gina Whipp

mdbGal,

Try this... (ON A BACK-UP COPY!!!) Has no error handling and could probably
use some tweaking but have fun!

'***Start of Code
Dim Msg, Style, Title, Response, MyString
Dim NewMaterialID As Long
Dim rst As Recordset
Dim FileCount As Integer
Dim Counter As Integer
Dim strSQL As String

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrReceivingNumber, mrCustomerIdentifier, mrLotNumber, mrAlloyID, mrTemperID,
mrGauge, mrWidth, mrLength, mrQuantity, mrNetWeight, mrGrossWeight ) " & _
"SELECT 'S' & [crCoilReceivingNumber],
tblLifts.LiftStr, tblCoilReceiving.crCoilLotNumber,
tblCoilReceiving.crAlloyID, tblCoilReceiving.crTemperID,
tblCoilReceiving.crCoilListGauge, tblCoilReceiving.crCoilListWidth,
tblOrderDetail.odLength, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight " & _
"FROM tblCoilReceiving INNER JOIN
(tblOrderDetail INNER JOIN tblLifts ON tblOrderDetail.odOrderDetailID =
tblLifts.lOrderDetailID) ON tblCoilReceiving.crCoilID = tblLifts.lCoilID " &
_
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"
DoCmd.SetWarnings True

Set rst = CurrentDb().OpenRecordset("tblMaterialReceiving")

FileCount = DCount("lCoilID", "tblLifts", "[lCoilID]=" &
Me![txtCoilID])
Counter = 0

If Me.chkTransfer = -1 Then
Msg = "You are about to transfer this lift, is that what you
want to do?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Transfer Lift"
Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
MyString = "Yes"

With rst
Do Until Counter = FileCount
NewMaterialID = DMax("mrMaterialID",
"tblMaterialReceiving") + 1
.Edit
!mrMaterialID = NewMaterialID
!mrReceivedDate = Me.txtTransferDate
!mrInventoryTransferDate = Me.txtTransferDate
!mrTransfer = True
!mrCustomerID = Me.cboTransferTo
!mrNonConformanceID = Me.txtNonConformanceID
!mrTransferFrom =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCustomerID]

strSQL = "UPDATE tblMaterialReceiving SET
tblMaterialReceiving.mrMaterialID = NewMaterialID WHERE
(((tblMaterialReceiving.mrMaterialID) Is Null));"

.Update
rst.MoveNext
Me.txtStatusID = 7
MsgBox "Be sure to tell the Receiving Department to
tag the Lift with Material Identification number " & NewMaterialID & "!",
vbOKOnly, "Material Receipt"
Counter = Counter + 1
Loop
.Close
End With

Set rst = Nothing
Else
MyString = "No"
DoCmd.CancelEvent
End If
End If
'****End of Code

NOTE: If mrMaterialID is set a PK then the above does not work properly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

mdbGal said:
Hello All,

I think this can be done but I'm not sure. I want to be able to transfer
multiple lifts to another table and assign them a new primary key at the
same
time, it's not working. I know I am missing something or maybe it really
can't be done this way? I THINK it has to do with the queries that use
transactions. Below is the code I am playing with but can't make it work.
There are lot of lines not in use but I fgured I should post all of the
left
that I was playing with.

Dim Msg, Style, Title, Response, MyString
Dim NewMaterialID As Long
Dim rst As Recordset
Dim FileCount As Integer
Dim Counter As Integer
Dim strSQL As String

Set rst = CurrentDb().OpenRecordset("tblMaterialReceiving")

FileCount = DCount("lCoilID", "tblLifts", "[lCoilID]=" &
Me![txtCoilID])
Counter = 0
NewMaterialID = DMax("mrMaterialID", "tblMaterialReceiving") + 1

If Me.chkTransfer = -1 Then
Msg = "You are about to transfer this lift, is that what you
want to do?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "Transfer Lift"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"

With rst
Do Until Counter = FileCount
NewMaterialID = DMax("mrMaterialID",
"tblMaterialReceiving")
+ 1
.AddNew
'!mrMaterialID = NewMaterialID
'!mrCoilID =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCoilID]
'!mrReceivedDate = Me.txtTransferDate
'!mrInventoryTransferDate = Date
'!mrTransfer = True
'!mrTransferFrom =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCustomerID]
'!mrCustomerID = Me.cboTransferTo
'!mrNonConformanceID = Me.txtNonConformanceID
'!mrAlloyID
'!mrTemperID
'!mrGauge
'!mrWidth
'!mrReceivingNumber
'!mrQuantity =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtPieces]
'!mrNetWeight =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtCalculateNet]
'!mrGrossWeight =
Forms![frmCoilRun]![sfrCoilRun].Form![sfrCoilRunDetail].Form![sfrLifts].Form![txtGrossWeight]

.Update
'DoCmd.SetWarnings False
'DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID,
mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT
DMax('mrMaterialID','tblMaterialReceiving')+1, 'S' &
[crCoilReceivingNumber],
tblLifts.lTransferDate, tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"

DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID,
mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT " & NewMaterialID & ", 'S' &
[crCoilReceivingNumber], tblLifts.lTransferDate,
tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"
.Update
rst.MoveNext 'put in
'rst.MoveFirst 'moved up
DoCmd.RunCommand acCmdSaveRecord
Me.txtStatusID = 7
MsgBox "Be sure to tell the Receiving Department to tag the
Lift with Material Identification number " & NewMaterialID & "!",
vbOKOnly,
"Material Receipt"
'DoCmd.SetWarnings True
Counter = Counter + 1
'rst.MoveNext 'put back seems to work best here?
Loop
.Close
End With
'DoCmd.OpenQuery "qupdTransferLifts"
'DoCmd.RunSQL "INSERT INTO tblMaterialReceiving (
mrMaterialID, mrReceivingNumber, mrReceivedDate, mrInventoryTransferDate,
mrTransfer, mrTransferFrom, mrCustomerIdentifier, mrCustomerID,
mrLotNumber,
mrAlloyID, mrTemperID, mrGauge, mrWidth, mrQuantity, mrNetWeight,
mrGrossWeight, mrNonConformanceID ) " & _
"SELECT " & NewMaterialID & ", 'S' &
[crCoilReceivingNumber], tblLifts.lTransferDate,
tblLifts.lTransferDate, -1,
tblCoilReceiving.crCustomerID, tblLifts.LiftStr, tblLifts.lTransferTo,
tblCoilReceiving.crCoilLotNumber, tblCoilReceiving.crAlloyID,
tblCoilReceiving.crTemperID, tblCoilReceiving.crCoilListGauge,
tblCoilReceiving.crCoilListWidth, tblLifts.lPieces, tblLifts.lCalcNet,
tblLifts.lGrossWeight, tblLifts.lNonConformanceID " & _
"FROM tblCoilReceiving INNER JOIN tblLifts ON
tblCoilReceiving.crCoilID = tblLifts.lCoilID " & _
"WHERE
(((tblLifts.lCoilID)=[Forms]![sfrLiftNonConformance]![txtCoilID]));"
Else
MyString = "No"
DoCmd.CancelEvent
End If
End If

Thanks to all who reply
Click to expand...
 
Top