Limit to List question

[Afrosheen via AccessMonster.com]

Good morning everyone.

I'm trying to use the limit to list as a sub routine. Here is the code:

Private Sub cklist()

10 On Error GoTo Combo38_NotInList_Error

20 intAnswer = MsgBox("The " & cbolist & " " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Item Entry")
30 If intAnswer = vbYes Then
40 'strSQL = "INSERT INTO current location([currlocn]) " & _
"VALUES ('" & NewData & "');"
50 DoCmd.SetWarnings False
60 DoCmd.RunSQL strSQL
70 DoCmd.SetWarnings True
80 MsgBox "The new " & cbolist & " has been added to the list." _
, vbInformation, "Item Entry"
90 Response = acDataErrAdded
100 Else
110 MsgBox "Please choose a " & cbolist & " from the list." _
, vbInformation, "Item Entry"
120 Response = acDataErrContinue
130 End If
End Sub

What is happening is that I'm getting an error on line 90 and probably on
line 120. Those lines have "Response" and the error code is telling me that
the variable was not found.

I'm trying use this for about 3-4 combo boxes. As I step through the code
everything else seems to work ok. The string strSQL, newdata, and cbolist are
set up as strings and intAnswer As Integer.

Can I use the Not in List like this or do I have to use it separately?

Thanks for your input and help.

 

[Daryl S]

What is the purpose of the 'Response'? Are you trying to return a value to
the calling routine? If so, then you need to make this a function instead of
a subroutine, and declare the data type of the return value. Then you set
the value you want to return to the name of the function (not to Response).
It would look like this:

Private Function cklist() As Integer

and this for the return values:
cklist = acDataErrContinue

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me so fast.
I did a break on the both Response =, and if I click on Yes {I want to add
it to the list} then it shows a number like this Response = 2

What is the purpose of the 'Response'? Are you trying to return a value to
the calling routine? If so, then you need to make this a function instead of
a subroutine, and declare the data type of the return value. Then you set
the value you want to return to the name of the function (not to Response).
It would look like this:

Private Function cklist() As Integer

and this for the return values:
cklist = acDataErrContinue

Good morning everyone.

[quoted text clipped - 35 lines]

Thanks for your input and help.

 

[Afrosheen via AccessMonster.com]

H Daryl,
I looked up the Response=acDataErrContinue and found out that it is Access
codes for their NotInList functions. It looks like it's bound to that
particular combo box also. So what I want to do may not work.

I appreciate the help and you getting back to me.

What is the purpose of the 'Response'? Are you trying to return a value to
the calling routine? If so, then you need to make this a function instead of
a subroutine, and declare the data type of the return value. Then you set
the value you want to return to the name of the function (not to Response).
It would look like this:

Private Function cklist() As Integer

and this for the return values:
cklist = acDataErrContinue

Good morning everyone.

[quoted text clipped - 35 lines]

Thanks for your input and help.

 

[Daryl S]

Afrosheen -

How do you want to use 'Response'? Do you want it sent back?

Are you just missing the Response in the subroutine header:

Private Sub cklist(Response As Integer)

If you add a Dim Response As Integer in the code, the error will go away,
but you have not used Response anywhere, so you can just remove any lines
with Response in them, unless you do want to return the value - then add it
in the subroutine header as shown above.

--
Daryl S

Thanks for getting back to me so fast.
I did a break on the both Response =, and if I click on Yes {I want to add
it to the list} then it shows a number like this Response = 2

What is the purpose of the 'Response'? Are you trying to return a value to
the calling routine? If so, then you need to make this a function instead of
a subroutine, and declare the data type of the return value. Then you set
the value you want to return to the name of the function (not to Response).
It would look like this:

Private Function cklist() As Integer

and this for the return values:
cklist = acDataErrContinue

Good morning everyone.

[quoted text clipped - 35 lines]

Thanks for your input and help.

 

[Afrosheen via AccessMonster.com]

Hi Marshall,
This is what I did. I put the Private Function in the same program and not a
module.

I got a "ByRef argument type mismatch" error.

Private Sub Combo40_NotInList(NewData As String, Response As Integer)
tbl = "methods"
fld = "methods"
cbolist = "method"

Response = cklist(NewData, tbl, fld)
End Sub

I had to declare the tbl and fld. Their names are calles methods

Thanks for your help.

I'm trying to use the limit to list as a sub routine. Here is the code:

[quoted text clipped - 31 lines]

Can I use the Not in List like this or do I have to use it separately?

You can not specify a Sub procedure in an event
**property**, but you can write a function for that purpose.
OTOH, the function would have to communicate with the
NotInList event so it can do its thing, but using a function
in the OnNotInList property does not have the capability to
set the function's arguments nor to process the function's
return value.

You can do this kind of thing:

Private Function cklist(NewData As String, tbl As String,
fld As String) As Integer
Dim strSQL As String
10 On Error GoTo Combo38_NotInList_Error

20 intAnswer = MsgBox("The " & cbolist & " " &
Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf
& _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Item Entry")
30 If intAnswer = vbYes Then
40 strSQL = "INSERT INTO [" & tbl & "] ([" & fld
& "]) VALUES ('" & NewData & "')"
60 CurrentDb.Execute strSQL, dbFailOnError
80 MsgBox "The new " & cbolist & " has been added
to the list." _
, vbInformation, "Item Entry"
90 Response = acDataErrAdded
100 Else
110 MsgBox "Please choose a " & cbolist & " from
the list." _
, vbInformation, "Item Entry"
120 Response = acDataErrContinue
130 End If
cklist = Response
End Sub

Then each individual NotInList procedure can call the
function:

Sub cbox_NotInList(NewData As String, Response As Integer)
Response = cklist(NewData, thetable, thefield)
End Sub

 

[Afrosheen via AccessMonster.com]

Ok, here's what I've done so far testing. In order to get the ByRef error to
stop I had to put quotes around the tbl and fld. {Which makes no sense to me,
but that's access}

response = cklist(NewData, "tbl", "fld")

When it ran I still got the Response error. I rem them out and it started to
work. The problem is that they're not showing up in the list, so I've been
trying {unsuccessfully} to re query the combo box in the AfterUpdate control.
I get the error now that I can't re query until the information is saved. I
know it's there, access doesn't know it's there

That's where I'm at now..

Hi Marshall,
This is what I did. I put the Private Function in the same program and not a
module.

I got a "ByRef argument type mismatch" error.

Private Sub Combo40_NotInList(NewData As String, Response As Integer)
tbl = "methods"
fld = "methods"
cbolist = "method"

Response = cklist(NewData, tbl, fld)
End Sub

I had to declare the tbl and fld. Their names are calles methods

Thanks for your help.
[quoted text clipped - 46 lines]

Response = cklist(NewData, thetable, thefield)
End Sub

 

[Marshall Barton]

The missing quotes are my fault, they should have been
there. The quotes are needed because the arguments need the
names of the table and field where the new data is to be
added. Without the quotes, Access tries to find the value
of those things, which don't have values in that context.

You need to replace the place holders tbl and fld with your
real names:
response = cklist(NewData, "methods", "methods")

The response error is also my fault. The two lines that set
Response should have been:
cklist = acDataErrAdded
cklist = acDataErrContinue
and the line
cklist = Response
should be removed.

The reason I suggested putting the code in a standard
module is so it can be used for more than one form. If the
function will only be used for this one form then it should
be in the form's module.
--
Marsh
MVP [MS Access]

Ok, here's what I've done so far testing. In order to get the ByRef error to
stop I had to put quotes around the tbl and fld. {Which makes no sense to me,
but that's access}

response = cklist(NewData, "tbl", "fld")

When it ran I still got the Response error. I rem them out and it started to
work. The problem is that they're not showing up in the list, so I've been
trying {unsuccessfully} to re query the combo box in the AfterUpdate control.
I get the error now that I can't re query until the information is saved. I
know it's there, access doesn't know it's there

Hi Marshall,
This is what I did. I put the Private Function in the same program and not a
module.

I got a "ByRef argument type mismatch" error.

Private Sub Combo40_NotInList(NewData As String, Response As Integer)
tbl = "methods"
fld = "methods"
cbolist = "method"

Response = cklist(NewData, tbl, fld)
End Sub

I had to declare the tbl and fld. Their names are calles methods

I'm trying to use the limit to list as a sub routine. Here is the code:

[quoted text clipped - 46 lines]

Response = cklist(NewData, thetable, thefield)
End Sub

 

[Afrosheen via AccessMonster.com]

After even further tests this is what I had to do to get it to work.


20 cbolist = "Method -"
30 strSQL = "INSERT INTO methods([methods]) VALUES ('" & NewData & "')"
40 response = cklist(NewData, "myTbl", "myFld")

In order for the "Response" to work I had to do:
Private Function cklist(NewData As String, tbl As String, fld As String) As
Integer
Dim Response As Integer

Now it works. Thanks for the direction and help. Again

I'm trying to use the limit to list as a sub routine. Here is the code:

[quoted text clipped - 31 lines]

Can I use the Not in List like this or do I have to use it separately?

You can not specify a Sub procedure in an event
**property**, but you can write a function for that purpose.
OTOH, the function would have to communicate with the
NotInList event so it can do its thing, but using a function
in the OnNotInList property does not have the capability to
set the function's arguments nor to process the function's
return value.

You can do this kind of thing:

Private Function cklist(NewData As String, tbl As String,
fld As String) As Integer
Dim strSQL As String
10 On Error GoTo Combo38_NotInList_Error

20 intAnswer = MsgBox("The " & cbolist & " " &
Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf
& _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Item Entry")
30 If intAnswer = vbYes Then
40 strSQL = "INSERT INTO [" & tbl & "] ([" & fld
& "]) VALUES ('" & NewData & "')"
60 CurrentDb.Execute strSQL, dbFailOnError
80 MsgBox "The new " & cbolist & " has been added
to the list." _
, vbInformation, "Item Entry"
90 Response = acDataErrAdded
100 Else
110 MsgBox "Please choose a " & cbolist & " from
the list." _
, vbInformation, "Item Entry"
120 Response = acDataErrContinue
130 End If
cklist = Response
End Sub

Then each individual NotInList procedure can call the
function:

Sub cbox_NotInList(NewData As String, Response As Integer)
Response = cklist(NewData, thetable, thefield)
End Sub

 

[BruceM via AccessMonster.com]

I would be surprised to learn you had compiled the code, as compiling should
have produced a Label Not Defined error because of this line:

After even further tests this is what I had to do to get it to work.

20 cbolist = "Method -"
30 strSQL = "INSERT INTO methods([methods]) VALUES ('" & NewData & "')"
40 response = cklist(NewData, "myTbl", "myFld")

In order for the "Response" to work I had to do:
Private Function cklist(NewData As String, tbl As String, fld As String) As
Integer
Dim Response As Integer

Now it works. Thanks for the direction and help. Again
[quoted text clipped - 46 lines]

Response = cklist(NewData, thetable, thefield)
End Sub

 

[Afrosheen via AccessMonster.com]

H Marshall,
I tried putting the table and field names like you suggested, but it was
giving me the "Not Defined" error.

Do I put
cklist = acDataErrAdded
cklist = acDataErrContinue

where the cklist = response is, or where the response = acDataErrAdded and so
fourth.?

The missing quotes are my fault, they should have been
there. The quotes are needed because the arguments need the
names of the table and field where the new data is to be
added. Without the quotes, Access tries to find the value
of those things, which don't have values in that context.

You need to replace the place holders tbl and fld with your
real names:
response = cklist(NewData, "methods", "methods")

The response error is also my fault. The two lines that set
Response should have been:
cklist = acDataErrAdded
cklist = acDataErrContinue
and the line
cklist = Response
should be removed.

The reason I suggested putting the code in a standard
module is so it can be used for more than one form. If the
function will only be used for this one form then it should
be in the form's module.

Ok, here's what I've done so far testing. In order to get the ByRef error to
stop I had to put quotes around the tbl and fld. {Which makes no sense to me,

[quoted text clipped - 29 lines]

 

[Afrosheen via AccessMonster.com]

UP Date:

I put it in a module and had to change a couple of things on it, but got it
working good now.
For some reason the "cbolist" is not going to the module. A minor thing.
I'll work on that.

BTW I did find out where the cklist= goes.. Must have be brain dead on that
one. Dame bramage.

Thanks for your help. I can use this routine in other programs I have and it
will be a lot easier.

H Marshall,
I tried putting the table and field names like you suggested, but it was
giving me the "Not Defined" error.

Do I put
cklist = acDataErrAdded
cklist = acDataErrContinue

where the cklist = response is, or where the response = acDataErrAdded and so
fourth.?

The missing quotes are my fault, they should have been
there. The quotes are needed because the arguments need the

[quoted text clipped - 23 lines]