Recordset Help

[ssatech]

Newbie to recordset:
I have a form called Products and subform called Product Details. Both recordsource from tables called Products and Product Details.
I need help inserting data from field name Location in the Products Subform into a field named Location1 in a table called “Company Transaction”.

I have no problem inserting data from Main form Products to Company Transaction table. It is the Product Details subform field name Location that I cannot seem to figure how to insert via a recordset.


Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim DB As Database, rstCompanyTrans As Recordset
Set DB = CurrentDb()
Set rstCompanyTrans = DB.OpenRecordset("Company Transactions", DB_OPEN_DYNASET)


rstCompanyTrans.AddNew
rstCompanyTrans!ProductID = ProductID
rstCompanyTrans!ConsignorID = ConsignorID
rstCompanyTrans!CustomerID = CustomerID
rstCompanyTrans!SpecNo = SpecNo
rstCompanyTrans!DANNumber = DANNumber
rstCompanyTrans!Packages = Packages
rstCompanyTrans!GoodsDescription = GoodsDescription
rstCompanyTrans!Condition = Condition
rstCompanyTrans!InStockDate = InStockDate


rstCompanyTrans.Update

Me!Allocated = True
Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
If Err = ERR_INVALIDUSENULL Then
MsgBox MSG_MUSTENTERREF, , CYBCAPT
Else
MsgBox Error$, , CYBCAPT
End If
Resume Exit_cmdUpdate_Click
End Sub

 

[TheAceMan1]

How are ya ssatech . . .

Perhaps:

[blue]rstCompanyTrans!Location1 = [[purple][b][i]subFormName[/i][/b][/purple]].Form!Location[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[irishjoe]

Have you tried the docmd.runSQL method?

Yes, its slow compared to using recordsets but can come in handy now and again.

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "insert into [Company Transactions](ProductID,ConsignorID) values ('" & ProductID& "','" & ConsignorID & "');"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


Something along those lines...

 

[JerryKlmns]

ssatech

Your recordset isn't closed but is expected to be destroyed when it goes out of scope. A suggestion also to ose a

With rstCompanyTrans
.AddNew
....
....
.Close
End With

block, which is faster since you refer to this object rstCompanyTrans only once. Also the action query irishjoe mentions, is faster and improved witt CurrentDB.Execute method which doesn't mess with warnings

 

[ssatech]

Hello AceMan, Irishjoe and Jerry,
Thanks very much for your expert inputs. I opted for the cheap way out and decided to use the Append query method, joining both tables and appending to Company Transaction table.

Very much appreciated - I now have other options based on your inputs to get the same result.