Cbo Not In List code error

  • Thread starter Thread starter Pamela
  • Start date Start date
P

Pamela

Found this code and tried to change it to fit my needs but it isn't updating
the cbo after I make the entry in the underlying tbl from which the cbo pulls
and I continue to get the Not In List error...One detail to consider is that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in Assn Table
but the entries are being viewed and entered as Text (Adjuster Name) in the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String, Response As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
 
You're setting Response equal to acDataErrContinue even if you've added the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there, but you
reset the field before the routine ends. Try putting an Else in that blank
line between End With and Response = acDataErrContinue)
 
I think I did as you instructed, but still getting an error that NotInList
code. After I enter the new record in the pop up form and close, the Not In
List error is still there and the original entry is still in the field. If I
hit No (to wanting to add it the Not In List name) I get RunTime error 3077:
Syntax error (comma) in expression and the debugger take it to this line of
code: Thanks again! Pamela
..FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String, Response As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

Douglas J. Steele said:
You're setting Response equal to acDataErrContinue even if you've added the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there, but you
reset the field before the routine ends. Try putting an Else in that blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
Found this code and tried to change it to fit my needs but it isn't
updating
the cbo after I make the entry in the underlying tbl from which the cbo
pulls
and I continue to get the Not In List error...One detail to consider is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in Assn
Table
but the entries are being viewed and entered as Text (Adjuster Name) in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String, Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
 
What is the value in NewData? (i.e.: what have you typed into the combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I think I did as you instructed, but still getting an error that NotInList
code. After I enter the new record in the pop up form and close, the Not
In
List error is still there and the original entry is still in the field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime error
3077:
Syntax error (comma) in expression and the debugger take it to this line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String, Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

Douglas J. Steele said:
You're setting Response equal to acDataErrContinue even if you've added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there, but
you
reset the field before the routine ends. Try putting an Else in that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
Found this code and tried to change it to fit my needs but it isn't
updating
the cbo after I make the entry in the underlying tbl from which the cbo
pulls
and I continue to get the Not In List error...One detail to consider is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in Assn
Table
but the entries are being viewed and entered as Text (Adjuster Name) in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
We are typing Text into the box...If I close out and go back in, the cbo DOES
reflect the entry -- so I don't think it's an issue of the adding it to that
tbl...but rather getting the cbo to requery or whatever it does to recognize
the entry....any other ideas how to do this??

Douglas J. Steele said:
What is the value in NewData? (i.e.: what have you typed into the combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I think I did as you instructed, but still getting an error that NotInList
code. After I enter the new record in the pop up form and close, the Not
In
List error is still there and the original entry is still in the field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime error
3077:
Syntax error (comma) in expression and the debugger take it to this line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String, Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

Douglas J. Steele said:
You're setting Response equal to acDataErrContinue even if you've added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there, but
you
reset the field before the routine ends. Try putting an Else in that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it isn't
updating
the cbo after I make the entry in the underlying tbl from which the cbo
pulls
and I continue to get the Not In List error...One detail to consider is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in Assn
Table
but the entries are being viewed and entered as Text (Adjuster Name) in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
If you're typing text, then you need quotes around the value:

..FindFirst "[Adjuster ID] = """ & NewData & """"

That's three double quotes before & NewData &, and four double quotes after.

As well, are you sure that it's Adjuster ID that contains the text? (My IDs
are usually numeric)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
We are typing Text into the box...If I close out and go back in, the cbo
DOES
reflect the entry -- so I don't think it's an issue of the adding it to
that
tbl...but rather getting the cbo to requery or whatever it does to
recognize
the entry....any other ideas how to do this??

Douglas J. Steele said:
What is the value in NewData? (i.e.: what have you typed into the combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I think I did as you instructed, but still getting an error that
NotInList
code. After I enter the new record in the pop up form and close, the
Not
In
List error is still there and the original entry is still in the field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime error
3077:
Syntax error (comma) in expression and the debugger take it to this
line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

:

You're setting Response equal to acDataErrContinue even if you've
added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there,
but
you
reset the field before the routine ends. Try putting an Else in that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it isn't
updating
the cbo after I make the entry in the underlying tbl from which the
cbo
pulls
and I continue to get the Not In List error...One detail to consider
is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in
Assn
Table
but the entries are being viewed and entered as Text (Adjuster Name)
in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
I went ahead and tried the changes you made but now am getting Runtime error
3079. Perhaps when I first explained, I wasn't clear back in Post1 about the
AutoNumber/vs. text...so I'll try again : )
tbl_Adjuster - pk of Adjuster ID (AutoNumber)
Assn Table - Adjuster ID fk (Number)
These 2 tables comprise qry_Assn from which frm_Assn is based
On frm_Assn, I have cbo Adjuster ID but because it isn't practical to
remember everyone's IDs, the Boundcolumn is Adjuster ID BUT it displays
Adjuster Name (format has 2 columns w/ column widths of 0";1") hence the user
will input text.
I know it's confusing and I'm sorry for that but thank so much for your
help...I'm really desperate to get it going! Pamela



Douglas J. Steele said:
If you're typing text, then you need quotes around the value:

..FindFirst "[Adjuster ID] = """ & NewData & """"

That's three double quotes before & NewData &, and four double quotes after.

As well, are you sure that it's Adjuster ID that contains the text? (My IDs
are usually numeric)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
We are typing Text into the box...If I close out and go back in, the cbo
DOES
reflect the entry -- so I don't think it's an issue of the adding it to
that
tbl...but rather getting the cbo to requery or whatever it does to
recognize
the entry....any other ideas how to do this??

Douglas J. Steele said:
What is the value in NewData? (i.e.: what have you typed into the combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I think I did as you instructed, but still getting an error that
NotInList
code. After I enter the new record in the pop up form and close, the
Not
In
List error is still there and the original entry is still in the field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime error
3077:
Syntax error (comma) in expression and the debugger take it to this
line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData, vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

:

You're setting Response equal to acDataErrContinue even if you've
added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in there,
but
you
reset the field before the routine ends. Try putting an Else in that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it isn't
updating
the cbo after I make the entry in the underlying tbl from which the
cbo
pulls
and I continue to get the Not In List error...One detail to consider
is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk in
Assn
Table
but the entries are being viewed and entered as Text (Adjuster Name)
in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
I kinda assumed that your FindFirst was incorrect.

Try:

..FindFirst "[Adjuster Name] = """ & NewData & """"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I went ahead and tried the changes you made but now am getting Runtime
error
3079. Perhaps when I first explained, I wasn't clear back in Post1 about
the
AutoNumber/vs. text...so I'll try again : )
tbl_Adjuster - pk of Adjuster ID (AutoNumber)
Assn Table - Adjuster ID fk (Number)
These 2 tables comprise qry_Assn from which frm_Assn is based
On frm_Assn, I have cbo Adjuster ID but because it isn't practical to
remember everyone's IDs, the Boundcolumn is Adjuster ID BUT it displays
Adjuster Name (format has 2 columns w/ column widths of 0";1") hence the
user
will input text.
I know it's confusing and I'm sorry for that but thank so much for your
help...I'm really desperate to get it going! Pamela



Douglas J. Steele said:
If you're typing text, then you need quotes around the value:

..FindFirst "[Adjuster ID] = """ & NewData & """"

That's three double quotes before & NewData &, and four double quotes
after.

As well, are you sure that it's Adjuster ID that contains the text? (My
IDs
are usually numeric)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
We are typing Text into the box...If I close out and go back in, the
cbo
DOES
reflect the entry -- so I don't think it's an issue of the adding it to
that
tbl...but rather getting the cbo to requery or whatever it does to
recognize
the entry....any other ideas how to do this??

:

What is the value in NewData? (i.e.: what have you typed into the
combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I think I did as you instructed, but still getting an error that
NotInList
code. After I enter the new record in the pop up form and close,
the
Not
In
List error is still there and the original entry is still in the
field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime
error
3077:
Syntax error (comma) in expression and the debugger take it to this
line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

:

You're setting Response equal to acDataErrContinue even if you've
added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in
there,
but
you
reset the field before the routine ends. Try putting an Else in
that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it
isn't
updating
the cbo after I make the entry in the underlying tbl from which
the
cbo
pulls
and I continue to get the Not In List error...One detail to
consider
is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk
in
Assn
Table
but the entries are being viewed and entered as Text (Adjuster
Name)
in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
Hi Doug,

Thanks, I made that change, but I don't see that it made any difference. It
still seems to not be recognizing the new entry. Thanks Pamela

Douglas J. Steele said:
I kinda assumed that your FindFirst was incorrect.

Try:

..FindFirst "[Adjuster Name] = """ & NewData & """"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I went ahead and tried the changes you made but now am getting Runtime
error
3079. Perhaps when I first explained, I wasn't clear back in Post1 about
the
AutoNumber/vs. text...so I'll try again : )
tbl_Adjuster - pk of Adjuster ID (AutoNumber)
Assn Table - Adjuster ID fk (Number)
These 2 tables comprise qry_Assn from which frm_Assn is based
On frm_Assn, I have cbo Adjuster ID but because it isn't practical to
remember everyone's IDs, the Boundcolumn is Adjuster ID BUT it displays
Adjuster Name (format has 2 columns w/ column widths of 0";1") hence the
user
will input text.
I know it's confusing and I'm sorry for that but thank so much for your
help...I'm really desperate to get it going! Pamela



Douglas J. Steele said:
If you're typing text, then you need quotes around the value:

..FindFirst "[Adjuster ID] = """ & NewData & """"

That's three double quotes before & NewData &, and four double quotes
after.

As well, are you sure that it's Adjuster ID that contains the text? (My
IDs
are usually numeric)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


We are typing Text into the box...If I close out and go back in, the
cbo
DOES
reflect the entry -- so I don't think it's an issue of the adding it to
that
tbl...but rather getting the cbo to requery or whatever it does to
recognize
the entry....any other ideas how to do this??

:

What is the value in NewData? (i.e.: what have you typed into the
combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I think I did as you instructed, but still getting an error that
NotInList
code. After I enter the new record in the pop up form and close,
the
Not
In
List error is still there and the original entry is still in the
field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime
error
3077:
Syntax error (comma) in expression and the debugger take it to this
line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

:

You're setting Response equal to acDataErrContinue even if you've
added
the
value to the list. If you've added it, you should be setting it to
acDataErrAdded. (Yes, I realize you have that line of code in
there,
but
you
reset the field before the routine ends. Try putting an Else in
that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it
isn't
updating
the cbo after I make the entry in the underlying tbl from which
the
cbo
pulls
and I continue to get the Not In List error...One detail to
consider
is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number fk
in
Assn
Table
but the entries are being viewed and entered as Text (Adjuster
Name)
in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
Hold on.

Me.Requery

requeries the entire form. Try the following instead:

Me!Assn_Table_Adjuster_ID.Requery

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pamela said:
Hi Doug,

Thanks, I made that change, but I don't see that it made any difference.
It
still seems to not be recognizing the new entry. Thanks Pamela

Douglas J. Steele said:
I kinda assumed that your FindFirst was incorrect.

Try:

..FindFirst "[Adjuster Name] = """ & NewData & """"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pamela said:
I went ahead and tried the changes you made but now am getting Runtime
error
3079. Perhaps when I first explained, I wasn't clear back in Post1
about
the
AutoNumber/vs. text...so I'll try again : )
tbl_Adjuster - pk of Adjuster ID (AutoNumber)
Assn Table - Adjuster ID fk (Number)
These 2 tables comprise qry_Assn from which frm_Assn is based
On frm_Assn, I have cbo Adjuster ID but because it isn't practical to
remember everyone's IDs, the Boundcolumn is Adjuster ID BUT it displays
Adjuster Name (format has 2 columns w/ column widths of 0";1") hence
the
user
will input text.
I know it's confusing and I'm sorry for that but thank so much for your
help...I'm really desperate to get it going! Pamela



:

If you're typing text, then you need quotes around the value:

..FindFirst "[Adjuster ID] = """ & NewData & """"

That's three double quotes before & NewData &, and four double quotes
after.

As well, are you sure that it's Adjuster ID that contains the text?
(My
IDs
are usually numeric)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


We are typing Text into the box...If I close out and go back in, the
cbo
DOES
reflect the entry -- so I don't think it's an issue of the adding it
to
that
tbl...but rather getting the cbo to requery or whatever it does to
recognize
the entry....any other ideas how to do this??

:

What is the value in NewData? (i.e.: what have you typed into the
combo
box?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I think I did as you instructed, but still getting an error that
NotInList
code. After I enter the new record in the pop up form and close,
the
Not
In
List error is still there and the original entry is still in the
field.
If I
hit No (to wanting to add it the Not In List name) I get RunTime
error
3077:
Syntax error (comma) in expression and the debugger take it to
this
line
of
code: Thanks again! Pamela
.FindFirst "[Adjuster ID] = " & NewData & ""
There isn't a comma in it so I don't understand what it means.
Just to make sure I did it correctly, here's my updated code:
Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then


DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

Response = acDataErrAdded
End If
End Sub

:

You're setting Response equal to acDataErrContinue even if
you've
added
the
value to the list. If you've added it, you should be setting it
to
acDataErrAdded. (Yes, I realize you have that line of code in
there,
but
you
reset the field before the routine ends. Try putting an Else in
that
blank
line between End With and Response = acDataErrContinue)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Found this code and tried to change it to fit my needs but it
isn't
updating
the cbo after I make the entry in the underlying tbl from
which
the
cbo
pulls
and I continue to get the Not In List error...One detail to
consider
is
that
the ID field is AutoNumber pk in the tbl_Adjuster and Number
fk
in
Assn
Table
but the entries are being viewed and entered as Text (Adjuster
Name)
in
the
cbo. Here is the code:

Private Sub Assn_Table_Adjuster_ID_NotInList(NewData As
String,
Response
As
Integer)
If MsgBox(NewData & " is not in the list - Add " & NewData,
vbQuestion
+ _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes
Then

DoCmd.OpenForm "frm_Adjuster", , , , acFormAdd,
acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone

.FindFirst "[Adjuster ID] = " & NewData & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Response = acDataErrContinue
End If
End Sub

Thanks so much for the help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top