Hi
I have an excel form which I need to be able to update tables within Access.

I dont have a problem with add new records or using the if field = item then
otherwise loop until eof.

However is there a way to use the findfirst so I dont have to search through
a table that has thousands of data so I can update a field.

Below is some of my code I currently am trying but keep getting stuck on the
rs1,findfirst

Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset
Dim qy As dao.QueryDef
Dim stDenom As String
Dim dbStoreNo As Double
Dim stType As String
Dim stWhere As String

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrkODBC
DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("", "admin", "")
Set db = wrkJet.OpenDatabase("G:\ProdCont\NZ Gift Vouchers\KMNZGV.mdb")
Set rs = db.OpenRecordset("tbl_Issues")

Application.DisplayAlerts = False 'Warning messages cannot pop-up
'''''''check which denomination has been ordered, this is required to help
with updating the database with _
the correct denomination against each voucher no'''''
If UserForm1.txtVfiveS.Value < "" Then
stDenom = "005"
ElseIf UserForm1.txtVtenS.Value < "" Then
stDenom = "010"
ElseIf UserForm1.txtVtwentyS.Value < "" Then
stDenom = "020"
ElseIf UserForm1.txtVfiftyS.Value < "" Then
stDenom = "050"
End If
''''''convert the provided store userid into a store number and set the
type''''
dbStoreNo = Left(UserForm1.lblName.Caption, 4) 'set 4 digit store
number by taking the first 4 characters only
If dbStoreNo < 9999 And dbStoreNo 1 Then
stType = "Store"
ElseIf dbStoreNo = 9999 Then
stType = "Corporate"
End If
'''''Update database so correct details are entered for each column as a
record of which voucher no has been _
allocated to specific store'''''
Stop
Do
BeginAgain:
If stDenom = "005" Then
dbVoucherStartNo = UserForm1.txtVfiveS.Value
dbVoucherEndNo = UserForm1.txtVfiveE.Value
ElseIf stDenom = "010" Then
dbVoucherStartNo = UserForm1.txtVtenS.Value
dbVoucherEndNo = UserForm1.txtVtenE.Value
ElseIf stDenom = "020" Then
dbVoucherStartNo = UserForm1.txtVtwentyS.Value
dbVoucherEndNo = UserForm1.txtVtwentyE.Value
ElseIf stDenom = "050" Then
dbVoucherStartNo = UserForm1.txtVfiftyS.Value
dbVoucherEndNo = UserForm1.txtVfiftyE.Value
End If
Set rs1 = db.OpenRecordset("tbl_Head_Office_Stock")
rs1.MoveFirst
Do
stvoucher = dbVoucherStartNo
rs.AddNew
rs.Fields("IssueDate") = Date
rs.Fields("VoucherID") = stDenom & stvoucher
rs.Fields("Store") = dbStoreNo
rs.Fields("Type") = stType
rs.Update
Do
stWhere = "(Denom = """ & stDenom & """) AND (Voucher = """ &
stvoucher & _
""") "
rs1.FindFirst stWhere ??????????????? GETING STUCK HERE ?????????
rs1.Edit
rs1.Fields("SendToStore") = True
rs1.Update
dbVoucherStartNo = dbVoucherStartNo + 1
Loop Until dbVoucherEndNo = dbVoucherStartNo - 1

There is more code here but it works great.

Thank you
Noemi

Not sure but try this

stWhere = "(((Denom) =" & stDenom & ") AND (Voucher) =" & stvoucher & ")) "

"Noemi" wrote:

Hi
I have an excel form which I need to be able to update tables within Access.

I dont have a problem with add new records or using the if field = item then
otherwise loop until eof.

However is there a way to use the findfirst so I dont have to search through
a table that has thousands of data so I can update a field.

Below is some of my code I currently am trying but keep getting stuck on the
rs1,findfirst

Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset
Dim qy As dao.QueryDef
Dim stDenom As String
Dim dbStoreNo As Double
Dim stType As String
Dim stWhere As String

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrkODBC
DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("", "admin", "")
Set db = wrkJet.OpenDatabase("G:\ProdCont\NZ Gift Vouchers\KMNZGV.mdb")
Set rs = db.OpenRecordset("tbl_Issues")

Application.DisplayAlerts = False 'Warning messages cannot pop-up
'''''''check which denomination has been ordered, this is required to help
with updating the database with _
the correct denomination against each voucher no'''''
If UserForm1.txtVfiveS.Value < "" Then
stDenom = "005"
ElseIf UserForm1.txtVtenS.Value < "" Then
stDenom = "010"
ElseIf UserForm1.txtVtwentyS.Value < "" Then
stDenom = "020"
ElseIf UserForm1.txtVfiftyS.Value < "" Then
stDenom = "050"
End If
''''''convert the provided store userid into a store number and set the
type''''
dbStoreNo = Left(UserForm1.lblName.Caption, 4) 'set 4 digit store
number by taking the first 4 characters only
If dbStoreNo < 9999 And dbStoreNo 1 Then
stType = "Store"
ElseIf dbStoreNo = 9999 Then
stType = "Corporate"
End If
'''''Update database so correct details are entered for each column as a
record of which voucher no has been _
allocated to specific store'''''
Stop
Do
BeginAgain:
If stDenom = "005" Then
dbVoucherStartNo = UserForm1.txtVfiveS.Value
dbVoucherEndNo = UserForm1.txtVfiveE.Value
ElseIf stDenom = "010" Then
dbVoucherStartNo = UserForm1.txtVtenS.Value
dbVoucherEndNo = UserForm1.txtVtenE.Value
ElseIf stDenom = "020" Then
dbVoucherStartNo = UserForm1.txtVtwentyS.Value
dbVoucherEndNo = UserForm1.txtVtwentyE.Value
ElseIf stDenom = "050" Then
dbVoucherStartNo = UserForm1.txtVfiftyS.Value
dbVoucherEndNo = UserForm1.txtVfiftyE.Value
End If
Set rs1 = db.OpenRecordset("tbl_Head_Office_Stock")
rs1.MoveFirst
Do
stvoucher = dbVoucherStartNo
rs.AddNew
rs.Fields("IssueDate") = Date
rs.Fields("VoucherID") = stDenom & stvoucher
rs.Fields("Store") = dbStoreNo
rs.Fields("Type") = stType
rs.Update
Do
stWhere = "(Denom = """ & stDenom & """) AND (Voucher = """ &
stvoucher & _
""") "
rs1.FindFirst stWhere ??????????????? GETING STUCK HERE ?????????
rs1.Edit
rs1.Fields("SendToStore") = True
rs1.Update
dbVoucherStartNo = dbVoucherStartNo + 1
Loop Until dbVoucherEndNo = dbVoucherStartNo - 1

There is more code here but it works great.

Thank you
Noemi

Hi Mike
Unfortunately I still get the following error.

'Operration is not supported for this type o fobject'

Any other ideas???

Thanks
Noemi

"Mike" wrote:

Not sure but try this

stWhere = "(((Denom) =" & stDenom & ") AND (Voucher) =" & stvoucher & ")) "

"Noemi" wrote:

Hi
I have an excel form which I need to be able to update tables within Access.

I dont have a problem with add new records or using the if field = item then
otherwise loop until eof.

However is there a way to use the findfirst so I dont have to search through
a table that has thousands of data so I can update a field.

Below is some of my code I currently am trying but keep getting stuck on the
rs1,findfirst

Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset
Dim qy As dao.QueryDef
Dim stDenom As String
Dim dbStoreNo As Double
Dim stType As String
Dim stWhere As String

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrkODBC
DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("", "admin", "")
Set db = wrkJet.OpenDatabase("G:\ProdCont\NZ Gift Vouchers\KMNZGV.mdb")
Set rs = db.OpenRecordset("tbl_Issues")

Application.DisplayAlerts = False 'Warning messages cannot pop-up
'''''''check which denomination has been ordered, this is required to help
with updating the database with _
the correct denomination against each voucher no'''''
If UserForm1.txtVfiveS.Value < "" Then
stDenom = "005"
ElseIf UserForm1.txtVtenS.Value < "" Then
stDenom = "010"
ElseIf UserForm1.txtVtwentyS.Value < "" Then
stDenom = "020"
ElseIf UserForm1.txtVfiftyS.Value < "" Then
stDenom = "050"
End If
''''''convert the provided store userid into a store number and set the
type''''
dbStoreNo = Left(UserForm1.lblName.Caption, 4) 'set 4 digit store
number by taking the first 4 characters only
If dbStoreNo < 9999 And dbStoreNo 1 Then
stType = "Store"
ElseIf dbStoreNo = 9999 Then
stType = "Corporate"
End If
'''''Update database so correct details are entered for each column as a
record of which voucher no has been _
allocated to specific store'''''
Stop
Do
BeginAgain:
If stDenom = "005" Then
dbVoucherStartNo = UserForm1.txtVfiveS.Value
dbVoucherEndNo = UserForm1.txtVfiveE.Value
ElseIf stDenom = "010" Then
dbVoucherStartNo = UserForm1.txtVtenS.Value
dbVoucherEndNo = UserForm1.txtVtenE.Value
ElseIf stDenom = "020" Then
dbVoucherStartNo = UserForm1.txtVtwentyS.Value
dbVoucherEndNo = UserForm1.txtVtwentyE.Value
ElseIf stDenom = "050" Then
dbVoucherStartNo = UserForm1.txtVfiftyS.Value
dbVoucherEndNo = UserForm1.txtVfiftyE.Value
End If
Set rs1 = db.OpenRecordset("tbl_Head_Office_Stock")
rs1.MoveFirst
Do
stvoucher = dbVoucherStartNo
rs.AddNew
rs.Fields("IssueDate") = Date
rs.Fields("VoucherID") = stDenom & stvoucher
rs.Fields("Store") = dbStoreNo
rs.Fields("Type") = stType
rs.Update
Do
stWhere = "(Denom = """ & stDenom & """) AND (Voucher = """ &
stvoucher & _
""") "
rs1.FindFirst stWhere ??????????????? GETING STUCK HERE ?????????
rs1.Edit
rs1.Fields("SendToStore") = True
rs1.Update
dbVoucherStartNo = dbVoucherStartNo + 1
Loop Until dbVoucherEndNo = dbVoucherStartNo - 1

There is more code here but it works great.

Thank you
Noemi

"Noemi" wrote:

Hi Mike
Unfortunately I still get the following error.

'Operration is not supported for this type o fobject'

Any other ideas???


Does it help if you change this line:

Set rs1 = db.OpenRecordset("tbl_Head_Office_Stock")

to:

Set rs1 = db.OpenRecordset("tbl_Head_Office_Stock", 2)

--
urkec