Help, Help with this code

[Stella Pieters via AccessMonster.com]

L.S.

I've been trying to make this code work without any succes.
I've a transaction table where I store all the records of sold items.
Ive used the following code, but I keep on receiving "Run-time error 2465",
Microsoft Office Access can't find the field "|" referred to in your
expression.

Private Sub Form_AfterUpdate()
'If Me.NewRecord Then

Dim rst As DAO.Recordset
Dim dbs As DAO.Database


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

If Me![Owndepomys] = False Then
[Transaction Table.Storeroom] = "FP02"
[Transaction table.TransactionDate] = Me![Datevisit]
[Transaction table.Artcode] = DLookup("[ArtCode]", "Contraceptives",
"[Description]=" & Forms![visits]!Description)
[Transaction table.TransactionDescription] = "Over the counter"
[Transaction table.UnitsSold] = Me![Qty]
[Transaction table.AmountQty] = [Transaction table.AmountQty] * Me!
[Contraceptives.Price]
rst.AddNew

End If
'End If

'err_Form_AFterUpdate:
Exit Sub
End Sub

I've posted this problem yesterday but did not receive any response. Could
you please help with this.

By the way I'm a newbie in VBA coding.

Thank you in advance.

Stella Pieters

 

[Lynn Trapp]

Try this instead:

If Me![Owndepomys] = False Then
rst.Storeroom = "FP02"
rst.TransactionDate = Me![Datevisit]
rst.Artcode = DLookup("[ArtCode]", "Contraceptives",
"[Description]=" & Forms![visits]!Description)
rst.TransactionDescription = "Over the counter"
rst.UnitsSold = Me![Qty]
rst.AmountQty = [Transaction table.AmountQty] * Me!
[Contraceptives.Price]
rst.AddNew

End If

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

L.S.

I've been trying to make this code work without any succes.
I've a transaction table where I store all the records of sold items.
Ive used the following code, but I keep on receiving "Run-time error
2465",
Microsoft Office Access can't find the field "|" referred to in your
expression.

Private Sub Form_AfterUpdate()
'If Me.NewRecord Then

Dim rst As DAO.Recordset
Dim dbs As DAO.Database


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

If Me![Owndepomys] = False Then
[Transaction Table.Storeroom] = "FP02"
[Transaction table.TransactionDate] = Me![Datevisit]
[Transaction table.Artcode] = DLookup("[ArtCode]", "Contraceptives",
"[Description]=" & Forms![visits]!Description)
[Transaction table.TransactionDescription] = "Over the counter"
[Transaction table.UnitsSold] = Me![Qty]
[Transaction table.AmountQty] = [Transaction table.AmountQty] * Me!
[Contraceptives.Price]
rst.AddNew

End If
'End If

'err_Form_AFterUpdate:
Exit Sub
End Sub

I've posted this problem yesterday but did not receive any response. Could
you please help with this.

By the way I'm a newbie in VBA coding.

Thank you in advance.

Stella Pieters

 

[George Nicholson]

1) Use of the AddNew method positions the recordset on a New record, so you
need to use it *before* you assign values to recordset fields or you'll be
modifying the existing values of whichever record you happen to be on rather
than creating a new record.
2) Use of the Update method is required to "save" changes to the record
you've just modified (new or existing). This partially explains why your
code didn't actually do any harm to existing records: it wasn't Updating
them. Therefore, changes would have simply been thrown away even if #1 & #3
were correct.
3) Changes should be made to the recordset fields, not the table fields (you
haven't opened the table, you've opened a copy of the table as a recordset).
I suspect this is what generated your error: Access wouldn't have had a clue
what you meant by
[Transaction Table.Storeroom] = "FP02"
4) Note the change to the 3rd DLookup argument:
DLookup("[ArtCode]", "Contraceptives", "[Description]= '" &
Forms![visits]!Description & "'")
Assuming Description is a text string, it needs to be prefaced and followed
by single quotations, as well as the double quotations you had. What you had
would have been correct if the variable was numeric.
5) From the code you provided, I don't see the need to open the recordset
unless the If condition is met. Therefore I moved everything inside the If
statement. This wouldn't have prevented your code from working, it's just
more effecient.

If Me![Owndepomys] = False Then
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)
rst.AddNew
rst!Storeroom = "FP02"
rst!TransactionDate = Me![Datevisit]
rst!Artcode = DLookup("[ArtCode]", "Contraceptives",
"[Description]='" & Forms![visits]!Description & "'")
rst!TransactionDescription = "Over the counter"
rst!UnitsSold = Me![Qty]
rst!AmountQty = rst!AmountQty * Me! [Contraceptives.Price]
rst.Update
Set rst = Nothing
Set db = Nothing
End If

HTH,
--
George Nicholson

Remove 'Junk' from return address.

L.S.

I've been trying to make this code work without any succes.
I've a transaction table where I store all the records of sold items.
Ive used the following code, but I keep on receiving "Run-time error
2465",
Microsoft Office Access can't find the field "|" referred to in your
expression.

Private Sub Form_AfterUpdate()
'If Me.NewRecord Then

Dim rst As DAO.Recordset
Dim dbs As DAO.Database


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

If Me![Owndepomys] = False Then
[Transaction Table.Storeroom] = "FP02"
[Transaction table.TransactionDate] = Me![Datevisit]
[Transaction table.Artcode] = DLookup("[ArtCode]", "Contraceptives",
"[Description]=" & Forms![visits]!Description)
[Transaction table.TransactionDescription] = "Over the counter"
[Transaction table.UnitsSold] = Me![Qty]
[Transaction table.AmountQty] = [Transaction table.AmountQty] * Me!
[Contraceptives.Price]
rst.AddNew

End If
'End If

'err_Form_AFterUpdate:
Exit Sub
End Sub

I've posted this problem yesterday but did not receive any response. Could
you please help with this.

By the way I'm a newbie in VBA coding.

Thank you in advance.

Stella Pieters