Combo Box NotInList - How To Add Data To Underlying Table

This is simple I know, but I can't get it to work completely for me. I want
a SIMPLE procedure to control a combo box "Contractor" in a form
"frmVendorRepair" that is refers a table "tblContractor". I want the ability
to inform the user that the requested entry is not in the table, and ask them
if they wanted to add it to the table or not. If they do, then I want the
procedure to put the newly entered data into the table as a new entry. The
next time the user accesses this form, and enters the new data they previusly
entered, then I want it to be in the table this time.
I have used "a bunch" of suggesated vba procedures, but none of them seem to
work for me. I will admit, I'm not the sharpest VBA knife in the drawer.
Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE
procedure....
--
10SNUT

Change ComboBox property Limit to list to No.

"10SNUT" wrote:

This is simple I know, but I can't get it to work completely for me. I want
a SIMPLE procedure to control a combo box "Contractor" in a form
"frmVendorRepair" that is refers a table "tblContractor". I want the ability
to inform the user that the requested entry is not in the table, and ask them
if they wanted to add it to the table or not. If they do, then I want the
procedure to put the newly entered data into the table as a new entry. The
next time the user accesses this form, and enters the new data they previusly
entered, then I want it to be in the table this time.
I have used "a bunch" of suggesated vba procedures, but none of them seem to
work for me. I will admit, I'm not the sharpest VBA knife in the drawer.
Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE
procedure....
--
10SNUT

wrote:

This is simple I know, but I can't get it to work completely for me.
I want a SIMPLE procedure to control a combo box "Contractor" in a
form "frmVendorRepair" that is refers a table "tblContractor". I want
the ability to inform the user that the requested entry is not in the
table, and ask them if they wanted to add it to the table or not. If
they do, then I want the procedure to put the newly entered data into
the table as a new entry. The next time the user accesses this form,
and enters the new data they previusly entered, then I want it to be
in the table this time. I have used "a bunch" of suggesated vba
procedures, but none of them seem to work for me. I will admit, I'm
not the sharpest VBA knife in the drawer. Any help will be greatly
appreciated. Remember, I'm looking for a SIMPLE procedure....

It would have been nice if you could have described "not working
completely" better. It is even better if you would have posted what you
had so far for us to review. Maybe this will help you.

I usually put a double check question just in case...like:

--- Warning, air code (Not tested) ---

Private Sub Contractor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Contractor_NotInList

'-- We may need to add another Contractor
Response = MsgBox("[" & NewData & "] " & _
"is not a current Contractor..." & vbCr & vbCr & _
"Would you like to add this New Contractor to the DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Contractor record
Dim db As DAO.Database
Dim MySql As String
Set db = CurrentDb()
MySql = "Insert Into tblContractor(ContractorName) " & _
"Values(""" & NewData & """)"
db.Execute MySql, dbFailOnError
' The next line will tell Access to requery the cbo!!
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_Contractor_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_Contractor_NotInList:
' Add error handling code here
Resume Exit_Contractor_NotInList

End Sub

It *is* air code so cut and paste and then compile and find
all of my typing errors. You will also have to use your field
names and probably add some more fields to the insert.

HTH

--
RuralGuy

Please reply to the newsgroup so all may benefit.

RuralGuy,
I tried your suggested procedure. I get to the instruction:
Dim db As DAO.Database
and get:
"Compile Error" User-defined type not detected
The "db As DAO.Database" section of that line of code is highlighted.
Whats up???

Also, You had asked what the details of the procedure I am using was. Here
is the procedu
Private Sub ContractorName_NotInList(NewData As String, Response As Integer)


If MsgBox("Do you want to add '" _
& NewData & "' to the list of contractors?", _
vbOKCancel, "Add New Item") = vbOK Then

' Remove new data from combo box so control can be requeried
' after the Form1 form is closed
DoCmd.RunCommand acCmdUndo

' Display form to collect data needed for the new record
DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData

'Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

This routine works except it gets the "system generated error" that the item
is not in the list AFTER it gives the question to ask me to add the new entry
or not. When I respond, I get the system generated error.

Any ideas will be appreciated.

10SNUT

--
10SNUT


"RuralGuy" wrote:

wrote:

This is simple I know, but I can't get it to work completely for me.
I want a SIMPLE procedure to control a combo box "Contractor" in a
form "frmVendorRepair" that is refers a table "tblContractor". I want
the ability to inform the user that the requested entry is not in the
table, and ask them if they wanted to add it to the table or not. If
they do, then I want the procedure to put the newly entered data into
the table as a new entry. The next time the user accesses this form,
and enters the new data they previusly entered, then I want it to be
in the table this time. I have used "a bunch" of suggesated vba
procedures, but none of them seem to work for me. I will admit, I'm
not the sharpest VBA knife in the drawer. Any help will be greatly
appreciated. Remember, I'm looking for a SIMPLE procedure....

It would have been nice if you could have described "not working
completely" better. It is even better if you would have posted what you
had so far for us to review. Maybe this will help you.

I usually put a double check question just in case...like:

--- Warning, air code (Not tested) ---

Private Sub Contractor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Contractor_NotInList

'-- We may need to add another Contractor
Response = MsgBox("[" & NewData & "] " & _
"is not a current Contractor..." & vbCr & vbCr & _
"Would you like to add this New Contractor to the DataBase?", vbYesNo)

If Response = vbYes Then
'-- Create a new Contractor record
Dim db As DAO.Database
Dim MySql As String
Set db = CurrentDb()
MySql = "Insert Into tblContractor(ContractorName) " & _
"Values(""" & NewData & """)"
db.Execute MySql, dbFailOnError
' The next line will tell Access to requery the cbo!!
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_Contractor_NotInList:
Resume Next
Set db = Nothing
Exit Sub

Err_Contractor_NotInList:
' Add error handling code here
Resume Exit_Contractor_NotInList

End Sub

It *is* air code so cut and paste and then compile and find
all of my typing errors. You will also have to use your field
names and probably add some more fields to the insert.

HTH

--
RuralGuy

Please reply to the newsgroup so all may benefit.

wrote:

RuralGuy,
I tried your suggested procedure. I get to the instruction:
Dim db As DAO.Database
and get:
"Compile Error" User-defined type not detected
The "db As DAO.Database" section of that line of code is highlighted.
Whats up???

Also, You had asked what the details of the procedure I am using was.
Here is the procedu


Private Sub ContractorName_NotInList(NewData As String, Response As
Integer)


If MsgBox("Do you want to add '" _
& NewData & "' to the list of contractors?", _
vbOKCancel, "Add New Item") = vbOK Then

' Remove new data from combo box so control can be requeried
' after the Form1 form is closed
*** Just comment out the next line and this code will work ***
DoCmd.RunCommand acCmdUndo

' Display form to collect data needed for the new record
DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog,
NewData

'Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

This routine works except it gets the "system generated error" that
the item is not in the list AFTER it gives the question to ask me to
add the new entry or not. When I respond, I get the system generated
error.

Any ideas will be appreciated.

10SNUT

I'm sorry. I should have mentioned that the routine requires a reference
to the DAO x.x Object Library. From viewing the code go to
ToolsReferences - scroll down to Microsoft DAO x.x Object Library and
check it. Then you should be able to compile the code.

Having said that - you don't need my code!! There's *almost* nothing wrong
with yours. Just don't perform the *UNDO* code. Just comment out the line
I marked.

Your code it great and thanks for posting it. You aren't using any DAO in
the rest of your application so why start now.

Post back with your results.



--
RuralGuy

Please reply to the newsgroup so all may benefit.

RuralGuy,
I did as suggested by commenting out the "undo" line, and it still gets the
system error after I respond to the question I have in the code. The system
error it gets is:

The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed
items.

Still open to suggestions. I feel like we're SOOOO close.

10SNUT
--
10SNUT


"RuralGuy" wrote:

wrote:

RuralGuy,
I tried your suggested procedure. I get to the instruction:
Dim db As DAO.Database
and get:
"Compile Error" User-defined type not detected
The "db As DAO.Database" section of that line of code is highlighted.
Whats up???

Also, You had asked what the details of the procedure I am using was.
Here is the procedu


Private Sub ContractorName_NotInList(NewData As String, Response As
Integer)


If MsgBox("Do you want to add '" _
& NewData & "' to the list of contractors?", _
vbOKCancel, "Add New Item") = vbOK Then

' Remove new data from combo box so control can be requeried
' after the Form1 form is closed
*** Just comment out the next line and this code will work ***
DoCmd.RunCommand acCmdUndo

' Display form to collect data needed for the new record
DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog,
NewData

'Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

This routine works except it gets the "system generated error" that
the item is not in the list AFTER it gives the question to ask me to
add the new entry or not. When I respond, I get the system generated
error.

Any ideas will be appreciated.

10SNUT

I'm sorry. I should have mentioned that the routine requires a reference
to the DAO x.x Object Library. From viewing the code go to
ToolsReferences - scroll down to Microsoft DAO x.x Object Library and
check it. Then you should be able to compile the code.

Having said that - you don't need my code!! There's *almost* nothing wrong
with yours. Just don't perform the *UNDO* code. Just comment out the line
I marked.

Your code it great and thanks for posting it. You aren't using any DAO in
the rest of your application so why start now.

Post back with your results.



--
RuralGuy

Please reply to the newsgroup so all may benefit.

wrote:

RuralGuy,
I did as suggested by commenting out the "undo" line, and it still
gets the system error after I respond to the question I have in the
code. The system error it gets is:

The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the
listed items.

Still open to suggestions. I feel like we're SOOOO close.

10SNUT

Hi 10SNUT,

Just so you know what is happening, the Response = acDataErrAdded tells
Access to Requery the ComboBox. It could be a timing issue if "Form1"
doesn't get "NewData" properly inserted into the RecordSource of the
ComboBox. "acDialog" should stop any code in this form from executing
until we return from "Form1".

Let's put a little diagnostic MsgBox in for now right after we come back
from "Form1"!

DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData
________________________________
'-- Diagnostic code to see what is going on
If IsNull(DLookup("ContractorName", "tblContractors", _
"[ContractorName] = '" & NewData & "'")) Then
MsgBox "[" & Newdata & "] is NOT in the Table yet!"
Else
MsgBox "[" & Newdata & "] IS in the Table!"
End If
___________________________________

You'll need to replace "ContractorName" twice and "tblContractors" with
the actual names of the Field and Table.

--
RuralGuy

Please reply to the newsgroup so all may benefit.

RuralGuy,
Thanks for the continued help!!
I entered the diagnostic code, and it responded with:
"NewData" is NOT in the Table yet!

Any suggestions at this point? Is there a way to delay the code execution
to allow the "NewData" to get installed into the RecordSource?

**** As an added piece of info, are these comments going to the
NewsGroup, and if not, what is required to post them to the NewsGroup? I
just thought this help you're giving me would be very helpful to others.

--
10SNUT


"RuralGuy" wrote:

wrote:

RuralGuy,
I did as suggested by commenting out the "undo" line, and it still
gets the system error after I respond to the question I have in the
code. The system error it gets is:

The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the
listed items.

Still open to suggestions. I feel like we're SOOOO close.

10SNUT

Hi 10SNUT,

Just so you know what is happening, the Response = acDataErrAdded tells
Access to Requery the ComboBox. It could be a timing issue if "Form1"
doesn't get "NewData" properly inserted into the RecordSource of the
ComboBox. "acDialog" should stop any code in this form from executing
until we return from "Form1".

Let's put a little diagnostic MsgBox in for now right after we come back
from "Form1"!

DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData
________________________________
'-- Diagnostic code to see what is going on
If IsNull(DLookup("ContractorName", "tblContractors", _
"[ContractorName] = '" & NewData & "'")) Then
MsgBox "[" & Newdata & "] is NOT in the Table yet!"
Else
MsgBox "[" & Newdata & "] IS in the Table!"
End If
___________________________________

You'll need to replace "ContractorName" twice and "tblContractors" with
the actual names of the Field and Table.

--
RuralGuy

Please reply to the newsgroup so all may benefit.

wrote:

RuralGuy,
Thanks for the continued help!!
I entered the diagnostic code, and it responded with:
"NewData" is NOT in the Table yet!

Any suggestions at this point? Is there a way to delay the code
execution to allow the "NewData" to get installed into the
RecordSource?

**** As an added piece of info, are these comments going to the
NewsGroup, and if not, what is required to post them to the NewsGroup?
I just thought this help you're giving me would be very helpful to
others.

Hi 10SNUT,

Yes, these comments are all going to the News Group. Anyone who cares
can follow what we are doing.

As far as the diagnostic code goes I expected it to have a MessageBox
that stated "[NewContractorName] is NOT in the Table yet!" Did it really
say "NewData" or were you just protecting some private information? I
expected it to show the "Contractor" you had just added.

I think we should look into the "Form1" code and determine how you are
adding this Contractor to the underlying table of this ComboBox.

Could you give me an idea of how Form1 works? Is it just bound to the
underlying table of this ComboBox and you just fill in all of the
controls and let Access save it when you close?

What version of Access are you using. Are all of the service packs
applied?

How do you close this Dialog form?

You are right, I believe we are getting closer to the problem!

--
RuralGuy

Please reply to the newsgroup so all may benefit.

Hi RuralGuy,
I will try to answer all your questions.

1. The routine is a simple "test" routine to get this function working. It
wil be used in a larger application when I get it working.
2. There is nothing secretive about the data in the "test" routine, as well
as the larger routine, but I thought a simple routine would make it simpler
to diagnose.
3. The response is actually the data I tried to enter into the combo box,
in this case, the number "4".
4. The sequence of events is as follows : The form has 4 other fields, and
I enter data into them - no problem. Then I get to the combo box field, and
I try to enter a "4". The underlying table, "tblContractors" has only one
entry, and it is "1 Contractor". When I enter the "4", I get the error
response that's in my routine: "Do you want to add '4' to the list of
contractors?", and there is a "OK" and a "Cancel" button on the question box.
I hit the "OK" button, and I get the diagnostic error response "(4) is NOT
in the Table yet!" with an "OK" button. I hit the "OK" button, and then I
get the system generated error: "The text you entered is not an item in the
list. Select an item from the list, or enter text that matches one of the
listed items." with an "OK" button. If I hit the "OK" button, I get the
original form displayed with the number "4" in the ContractorName field, and
below that, There is a box with the only entry in the tblContractors table,
"1 Contractor". There is an "EXIT" button on the form. If I hit it, the
form closes.
5. All entries on the "Form1" are simply entries dragged from the table
list while the form is in Design mode. The ContractorName box was created by
using the "ComboBox" button in the Form menu. The properties of the
ContractName field a Both the Name and Control Source are
"ContractorName". The Row source is "SELECT
[tblContractors].[ContractorName] FROM tblContractors;. The RowSource Type
is Table/Query.
6. All fields on the form are text fields with the std. length of 50
characters. All fields are bound to the tblContractors table.
7. I'm using Access 2000, version (9.0 4402 SR-1).

That covers about all you asked. The actual application I use is only about
370KB. Is there any way to send that to you without giving out my actual
email address?

Hope this helps in the resolution. Thanks again.
10SNUT.
--
10SNUT


"RuralGuy" wrote:

wrote:

RuralGuy,
Thanks for the continued help!!
I entered the diagnostic code, and it responded with:
"NewData" is NOT in the Table yet!

Any suggestions at this point? Is there a way to delay the code
execution to allow the "NewData" to get installed into the
RecordSource?

**** As an added piece of info, are these comments going to the
NewsGroup, and if not, what is required to post them to the NewsGroup?
I just thought this help you're giving me would be very helpful to
others.

Hi 10SNUT,

Yes, these comments are all going to the News Group. Anyone who cares
can follow what we are doing.

As far as the diagnostic code goes I expected it to have a MessageBox
that stated "[NewContractorName] is NOT in the Table yet!" Did it really
say "NewData" or were you just protecting some private information? I
expected it to show the "Contractor" you had just added.

I think we should look into the "Form1" code and determine how you are
adding this Contractor to the underlying table of this ComboBox.

Could you give me an idea of how Form1 works? Is it just bound to the
underlying table of this ComboBox and you just fill in all of the
controls and let Access save it when you close?

What version of Access are you using. Are all of the service packs
applied?

How do you close this Dialog form?

You are right, I believe we are getting closer to the problem!

--
RuralGuy

Please reply to the newsgroup so all may benefit.