Access Mysql

  • Thread starter Thread starter Len
  • Start date Start date
L

Len

The funtion below worked when it was running on the Access Database but
fails now.
I have linked the tables from the MySQL Database using the same names as
they were before.
The form above this code can see the information in the tables this code is
under the save button.

Can anyone help me in changeing this to work with the MySQL database.

Private Sub Save_Click()
On Error GoTo Err_Save_Click

Dim db As
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim l(13) As Long
Dim s1 As Single
Dim t(7) As String
Dim d1 As Date


Set db = CurrentDb()
Set rs1 = OpenRecordset("Supply", dbOpenDynaset)
Set rs2 = OpenRecordset("Invertory", dbOpenDynaset)

d1 = Me![Date]
l(2) = Me![AuctionID]
l(3) = Me![TrollyNo]
t(1) = Me![Location]
l(4) = Me![CustomerID]
t(2) = Me![DocketNo]
l(5) = Me![ProductID]
t(3) = Me![ColourID]
t(4) = Me![g1] & Me![g2] & Me![g3]
t(5) = Me![Size] & Me![Combo50]
t(6) = Me![UnitType]
l(6) = Me![UnitLots]
l(7) = Me![Lots]
l(8) = Me![MinBuy]
l(9) = Me![MinPrice]
l(10) = Me![StartPrice]
l(11) = Me![Weight]
s1 = Me![Com]


With rs1
..MoveLast
l(1) = (rs1![SupplyID] + 1)
.AddNew
rs1![SupplyID] = l(1)
rs1![CustomerID] = l(4)
rs1![DocketNo] = t(2)
rs1![SupplyDate] = d1
rs1![AuctionID] = l(2)
rs1![ProductID] = l(5)
rs1![ColourID] = t(3)
rs1![Grade] = t(4)
rs1![Size] = t(5)
rs1![UnitType] = t(6)
rs1![UnitsLots] = l(6)
rs1![Lots] = l(7)
rs1![MinBuy] = l(8)
rs1![MinPrice] = l(9)
rs1![Weight] = l(11)
rs1![Com] = s1
.Update
.MoveLast
Me![SupplyID] = rs1![SupplyID]
..Close
End With

With rs2
.AddNew
rs2![SupplyID] = l(1)
rs2![AuctionID] = l(2)
rs2![ClockNo] = 1
rs2![Trolly] = l(3)
rs2![Location] = t(1)
rs2![CustomerID] = l(4)
rs2![SupplyDate] = d1
rs2![ProductID] = l(5)
rs2![ColourID] = t(3)
rs2![Grade] = t(4)
rs2![Size] = t(5)
rs2![UnitType] = t(6)
rs2![UnitsLots] = l(6)
rs2![Lots] = l(7)
rs2![MinBuy] = l(8)
rs2![MinPrice] = l(9)
rs2![StartPrice] = l(10)
rs2![Weight] = l(11)
.Update
..Close
End With

Me![Copy].SetFocus
Me![Save].Visible = False

Dim stDocName As String

stDocName = "R_OfferSlid"
DoCmd.OpenReport stDocName, acNormal



Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub
 
Set rs2 = OpenRecordset("Invertory", dbOpenDynaset)
should this say "Inventory", dbOpenDynaset?
Could that be part your problem?
 
I am not familiar with MySQL but some ODBC drivers are Read-Only.

You are using CurrentDb and ODBC-linked Tables so you need to check whether
the ODBC driver for MySQL is Read/Write or Read-Only.
 
Back
Top