AfterUpdate help

[mattc66 via AccessMonster.com]

Below Code is not working. I have used this code before - so I am not sure
what is wrong. In the LookUpItem drop down I have added the AfterUpdate
command. If it find the item in the drop down box it goes to that record. If
it doesn't find the item in the drop down it adds that item.

Private Sub LookUpItem_AfterUpdate()

Dim SID As String
Dim rsc As DAO.Recordset
Dim db As Database

Set rsc = Me.RecordsetClone
Set db = CurrentDb

SID = Me.LookUpItem.Value

'Record not found add record
If DCount("ItemComponent", "tblItemComponent", SID) = 0 Then

DoCmd.GoToRecord , , acNewRec
Me.ItemComponent = Me.LookUpItem.Column(0)
Me.ItemDescription = Me.LookUpItem.Column(1)
Me.Refresh

ElseIf DCount("ItemComponent", "tblItemComponent", SID) >= 1 Then

'Go to record of original Number
rsc.FindFirst SID
Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing

End Sub

 

[Ken Snell MVP]

Not sure if this is needed, but add a .MoveFirst action before you do the
find. And, you must build a criterion expression for the FindFirst method to
work. Just giving the SID value is not enough (unless SID is a full
expression string). Replace NameOfField with the real field name:

'Go to record of original Number
rst.MoveFirst
rsc.FindFirst "NameOfField ='" & SID & "'"
Me.Bookmark = rsc.Bookmark

Similar issue for your DCount expressions.

 

[Douglas J. Steele]

SID needs to be a Where clause (without the keyword Where)

In other words, it needs to be something like

SID = "SomeFieldName = " & Me.LookUpItem.Value

if SomeFieldName is a numeric field, or if it's a text field, something like

SID = "SomeFieldName = '" & Me.LookUpItem.Value & "'"

 

[mattc66 via AccessMonster.com]

That worked.. Thank you

SID needs to be a Where clause (without the keyword Where)

In other words, it needs to be something like

SID = "SomeFieldName = " & Me.LookUpItem.Value

if SomeFieldName is a numeric field, or if it's a text field, something like

SID = "SomeFieldName = '" & Me.LookUpItem.Value & "'"

Below Code is not working. I have used this code before - so I am not sure
what is wrong. In the LookUpItem drop down I have added the AfterUpdate

[quoted text clipped - 32 lines]

End Sub

 

[mattc66 via AccessMonster.com]

Correction - it works to add a record not found, but if the record is already
in the table it try to add it anyway. It's like it is ignoring the DLookup.

SID needs to be a Where clause (without the keyword Where)

In other words, it needs to be something like

SID = "SomeFieldName = " & Me.LookUpItem.Value

if SomeFieldName is a numeric field, or if it's a text field, something like

SID = "SomeFieldName = '" & Me.LookUpItem.Value & "'"

Below Code is not working. I have used this code before - so I am not sure
what is wrong. In the LookUpItem drop down I have added the AfterUpdate

[quoted text clipped - 32 lines]

End Sub

 

[Douglas J. Steele]

Try:

Private Sub LookUpItem_AfterUpdate()

Dim SID As String
Dim rsc As DAO.Recordset

SID = "ItemComponent = " & Me.LookUpItem

Set rsc = Me.RecordsetClone
rsc.FindFirst SID

If rsc.NoMatch Then
'Record not found add record
DoCmd.GoToRecord , , acNewRec
Me.ItemComponent = Me.LookUpItem.Column(0)
Me.ItemDescription = Me.LookUpItem.Column(1)
Me.Refresh
Else
'Go to record of original Number
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Correction - it works to add a record not found, but if the record is
already
in the table it try to add it anyway. It's like it is ignoring the
DLookup.

SID needs to be a Where clause (without the keyword Where)

In other words, it needs to be something like

SID = "SomeFieldName = " & Me.LookUpItem.Value

if SomeFieldName is a numeric field, or if it's a text field, something
like

SID = "SomeFieldName = '" & Me.LookUpItem.Value & "'"

Below Code is not working. I have used this code before - so I am not
sure
what is wrong. In the LookUpItem drop down I have added the AfterUpdate

[quoted text clipped - 32 lines]

End Sub