Searching field on the table

[KimTong via AccessMonster.com]

Hi,

Is anyone can tell me how to write in VB to search data from prompt to
exesting table?. Eg:
If the field that we input already have in our 'table1' send me msg 'Data
already in the table' otherwise add that data to the table1.

Tahnk you in advance

KF

 

[Guest]

Use the Before Update event of the control bound to the field you want to
search.

Private Sub Field1_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Field1]","Table1") Then
MsgBox "Data Already In The Table"
Cancel = True
End If

End Sub

 

[KimTong via AccessMonster.com]

Hi,

After I wrote this VB, even I put Cancel = True, the message box comes up
"You cancelled the previous operation'. Do you guys know why this message
comes up?

Thank you in advance.

KF

Use the Before Update event of the control bound to the field you want to
search.

Private Sub Field1_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Field1]","Table1") Then
MsgBox "Data Already In The Table"
Cancel = True
End If

End Sub

Hi,

[quoted text clipped - 6 lines]

KF

 

[Guest]

There is a typo in this line of code, my bad:
If Not IsNull(DLookup("[Field1]","Table1") Then
Should be
If Not IsNull(DLookup("[Field1]","Table1")) Then
--
Dave Hargis, Microsoft Access MVP

Hi,

After I wrote this VB, even I put Cancel = True, the message box comes up
"You cancelled the previous operation'. Do you guys know why this message
comes up?

Thank you in advance.

KF

Use the Before Update event of the control bound to the field you want to
search.

Private Sub Field1_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Field1]","Table1") Then
MsgBox "Data Already In The Table"
Cancel = True
End If

End Sub

Hi,

[quoted text clipped - 6 lines]

KF

 

[KimTong via AccessMonster.com]

I did put ')' (1 more closed bracket ), and the syntax is ok. But after I run
the program, Access gives a message 'You cancelled the previous operation'.
Do you know why it happened like that?

KF

There is a typo in this line of code, my bad:
If Not IsNull(DLookup("[Field1]","Table1") Then
Should be
If Not IsNull(DLookup("[Field1]","Table1")) Then

Hi,

[quoted text clipped - 23 lines]

 

[Guest]

On which line does the error occur?
The most common cause of this problem is an incorrectly named field or
table. this would happen on the DLookup line.
--
Dave Hargis, Microsoft Access MVP

I did put ')' (1 more closed bracket ), and the syntax is ok. But after I run
the program, Access gives a message 'You cancelled the previous operation'.
Do you know why it happened like that?

KF

There is a typo in this line of code, my bad:
If Not IsNull(DLookup("[Field1]","Table1") Then
Should be
If Not IsNull(DLookup("[Field1]","Table1")) Then

Hi,

[quoted text clipped - 23 lines]

KF

 

[KimTong via AccessMonster.com]

Hi,

Here are all my VB scrip:

Private Sub Combo28_BeforeUpdate(Cancel As Integer)
Dim strSql As String
Dim I As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared

If Me.Agent = "" Then Exit Sub
If IsNull(DLookup("Me.Agent", "Agent")) Then
Msg = "'" & Me.Agent & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

I = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Product type...")
If I = vbYes Then
strSql = "Insert Into agent([agent]) " & _
"values ('" & Me.Agent & "');"
CurrentDb.Execute strSql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


strSql = "Insert Into agent([agent]) " & _
"values ('" & Me.Agent & "');"
Else
MsgBox "Data Already in Table"
Cancel = True
End If
End Sub


The error message on: If IsNull(DLookup("Me.Agent", "Agent")) Then

On which line does the error occur?
The most common cause of this problem is an incorrectly named field or
table. this would happen on the DLookup line.

I did put ')' (1 more closed bracket ), and the syntax is ok. But after I run
the program, Access gives a message 'You cancelled the previous operation'.

[quoted text clipped - 11 lines]

 

[Guest]

Okay, thanks for posting the code, now I can help. The immediate problem
with the DLookup is you have the arguments in the wrong positions. Now, the
real problem is you are doing things in the wrong places. For what you are
doing, the before update event is not the place to do it. Part of your code
belongs in the After Update event and the rest of it belongs in the Not In
List Event. Here is a rewrite that shows how it should be done. First, you
use the combo's After Update event to make the record for the selected Agent
the currrent record for the form. When the Agent selected is not in the
combo's row source, you use the combo's Not In List event to give the user
the option to either add the Agent or cancel the selection and choose another:

Private Sub Combo28_AfterUpdate()

If Not IsNull(Me.Combo28) Then
With Me.RecordsetClone
.FindFirst "[Agent] = '" & Me.Combo28
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End sub
-------------
Private Sub Combo28_NotInList(NewData As String, Response As Integer)
Dim strSql As String

If MsgBox(NewData & " Is Not Currently in the List" & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.Combo28 = Me.Combo28.OldValue
CurrentDb.Execute ("INSERT INTO Agent([Agent]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.Combo28.Requery
Else
Response = acDataErrContinue
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Hi,

Here are all my VB scrip:

Private Sub Combo28_BeforeUpdate(Cancel As Integer)
Dim strSql As String
Dim I As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared

If Me.Agent = "" Then Exit Sub
If IsNull(DLookup("Me.Agent", "Agent")) Then
Msg = "'" & Me.Agent & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

I = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Product type...")
If I = vbYes Then
strSql = "Insert Into agent([agent]) " & _
"values ('" & Me.Agent & "');"
CurrentDb.Execute strSql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


strSql = "Insert Into agent([agent]) " & _
"values ('" & Me.Agent & "');"
Else
MsgBox "Data Already in Table"
Cancel = True
End If
End Sub


The error message on: If IsNull(DLookup("Me.Agent", "Agent")) Then

On which line does the error occur?
The most common cause of this problem is an incorrectly named field or
table. this would happen on the DLookup line.

I did put ')' (1 more closed bracket ), and the syntax is ok. But after I run
the program, Access gives a message 'You cancelled the previous operation'.

[quoted text clipped - 11 lines]

KF

 

[KimTong via AccessMonster.com]

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

.FindFirst "[Agent] = '" & Me.Combo28

Could you correct it for me please? I really appreaciate it.

KF

Okay, thanks for posting the code, now I can help. The immediate problem
with the DLookup is you have the arguments in the wrong positions. Now, the
real problem is you are doing things in the wrong places. For what you are
doing, the before update event is not the place to do it. Part of your code
belongs in the After Update event and the rest of it belongs in the Not In
List Event. Here is a rewrite that shows how it should be done. First, you
use the combo's After Update event to make the record for the selected Agent
the currrent record for the form. When the Agent selected is not in the
combo's row source, you use the combo's Not In List event to give the user
the option to either add the Agent or cancel the selection and choose another:

Private Sub Combo28_AfterUpdate()

If Not IsNull(Me.Combo28) Then
With Me.RecordsetClone
.FindFirst "[Agent] = '" & Me.Combo28
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End sub
-------------
Private Sub Combo28_NotInList(NewData As String, Response As Integer)
Dim strSql As String

If MsgBox(NewData & " Is Not Currently in the List" & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.Combo28 = Me.Combo28.OldValue
CurrentDb.Execute ("INSERT INTO Agent([Agent]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.Combo28.Requery
Else
Response = acDataErrContinue
End If
End Sub

Hi,

[quoted text clipped - 41 lines]

 

[John Spencer]

.FindFirst "[Agent] = '" & Me.Combo28 & "'"

OR
.FindFirst "[Agent] = " & Me.Combo28

It depends on whether or not Agent is a text field or a number field.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

.FindFirst "[Agent] = '" & Me.Combo28

Could you correct it for me please? I really appreaciate it.

KF

Okay, thanks for posting the code, now I can help. The immediate problem
with the DLookup is you have the arguments in the wrong positions. Now,
the
real problem is you are doing things in the wrong places. For what you
are
doing, the before update event is not the place to do it. Part of your
code
belongs in the After Update event and the rest of it belongs in the Not In
List Event. Here is a rewrite that shows how it should be done. First,
you
use the combo's After Update event to make the record for the selected
Agent
the currrent record for the form. When the Agent selected is not in the
combo's row source, you use the combo's Not In List event to give the user
the option to either add the Agent or cancel the selection and choose
another:

Private Sub Combo28_AfterUpdate()

If Not IsNull(Me.Combo28) Then
With Me.RecordsetClone
.FindFirst "[Agent] = '" & Me.Combo28
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End sub
-------------
Private Sub Combo28_NotInList(NewData As String, Response As Integer)
Dim strSql As String

If MsgBox(NewData & " Is Not Currently in the List" & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.Combo28 = Me.Combo28.OldValue
CurrentDb.Execute ("INSERT INTO Agent([Agent]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.Combo28.Requery
Else
Response = acDataErrContinue
End If
End Sub

Hi,

[quoted text clipped - 41 lines]

KF

 

[Guest]

John's answer is correct. Sorry about the typo.
One thing I may not have mentioned is that in the properties dialog for the
combo box, you need to set the Limit To List property to Yes.
--
Dave Hargis, Microsoft Access MVP

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

.FindFirst "[Agent] = '" & Me.Combo28

Could you correct it for me please? I really appreaciate it.

KF

Okay, thanks for posting the code, now I can help. The immediate problem
with the DLookup is you have the arguments in the wrong positions. Now, the
real problem is you are doing things in the wrong places. For what you are
doing, the before update event is not the place to do it. Part of your code
belongs in the After Update event and the rest of it belongs in the Not In
List Event. Here is a rewrite that shows how it should be done. First, you
use the combo's After Update event to make the record for the selected Agent
the currrent record for the form. When the Agent selected is not in the
combo's row source, you use the combo's Not In List event to give the user
the option to either add the Agent or cancel the selection and choose another:

Private Sub Combo28_AfterUpdate()

If Not IsNull(Me.Combo28) Then
With Me.RecordsetClone
.FindFirst "[Agent] = '" & Me.Combo28
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End sub
-------------
Private Sub Combo28_NotInList(NewData As String, Response As Integer)
Dim strSql As String

If MsgBox(NewData & " Is Not Currently in the List" & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.Combo28 = Me.Combo28.OldValue
CurrentDb.Execute ("INSERT INTO Agent([Agent]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.Combo28.Requery
Else
Response = acDataErrContinue
End If
End Sub

Hi,

[quoted text clipped - 41 lines]

KF

 

[KimTong via AccessMonster.com]

Ok. I just change the syntax, it doesn't give me an error message again. But,
it seems Access doesn't run the 'On Not in List' event. Because when I enter
a new data (not in the combo box list), it doesn't append the data in table
Agent automatically.

Could you double check it again? Thank you, I really appreciate it.

KF

.FindFirst "[Agent] = '" & Me.Combo28 & "'"

OR
.FindFirst "[Agent] = " & Me.Combo28

It depends on whether or not Agent is a text field or a number field.

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

[quoted text clipped - 55 lines]

 

[Guest]

If you look directly in the table, you should see the new record there. The
problem is it is added to the table, but the form's recordsource doesn't
include it. You have to requery the form to make that happen. Here is code
added to the procedure that should cure that problem.

Private Sub Combo28_NotInList(NewData As String, Response As Integer)
Dim strSql As String

If MsgBox(NewData & " Is Not Currently in the List" & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.Combo28 = Me.Combo28.OldValue
CurrentDb.Execute ("INSERT INTO Agent([Agent]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.Combo28.Requery
'New Code
Me.Requery
With Me.RecordsetClone
.FindFirst "[Agent] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Response = acDataErrContinue
End If
End Sub


--
Dave Hargis, Microsoft Access MVP

Ok. I just change the syntax, it doesn't give me an error message again. But,
it seems Access doesn't run the 'On Not in List' event. Because when I enter
a new data (not in the combo box list), it doesn't append the data in table
Agent automatically.

Could you double check it again? Thank you, I really appreciate it.

KF

.FindFirst "[Agent] = '" & Me.Combo28 & "'"

OR
.FindFirst "[Agent] = " & Me.Combo28

It depends on whether or not Agent is a text field or a number field.

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

[quoted text clipped - 55 lines]

KF

 

[KimTong via AccessMonster.com]

Yes. It works, thank you very much Klatuu & John..

KF

John's answer is correct. Sorry about the typo.
One thing I may not have mentioned is that in the properties dialog for the
combo box, you need to set the Limit To List property to Yes.

Thank you very much for you detail answers. I did follow all your advice
below, but VB still gives me an error message on:

[quoted text clipped - 49 lines]

 

[KimTong via AccessMonster.com]

Sorry. I still have a little problem. When I enter data, if the data on Combo
Box List then I picked 1 of the data on the combo Box, the cursor goes to
another record (it doesn't go to the next field on the same record). Is
anybody know why it happened like that?

KF

Yes. It works, thank you very much Klatuu & John..

KF

John's answer is correct. Sorry about the typo.
One thing I may not have mentioned is that in the properties dialog for the

[quoted text clipped - 4 lines]

 

[KimTong via AccessMonster.com]

Hi all,

I just realized, when I pick an agent name on the combo box, the cursor goes
to the first record that has the agent name that I picked. I think because of
this syntax: .

.FindFirst "[Agent] = '" & Me.Combo28 & "'"

It seems, VB is looking for the record that I just enter/pick from the first
record to the end. If VB find it on the agent table, cursor sill stay on that
particular record. Can I change or add more sytax, so the cursor will stay on
the current record? I still can't figure it out, is anyone in the forum can
tell me how? Thank you in advance.


KF