Combo Box NotInList - How To Add Data To Underlying Table

[Guest]

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....

 

[Guest]

Change ComboBox property Limit to list to No.

 

[RuralGuy]

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

 

[Guest]

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 procedure:
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

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]

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 procedure:


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
Tools>References - 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.

 

[Guest]

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

 

[RuralGuy]

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.

 

[Guest]

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,
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]

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!

 

[Guest]

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 are: 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,
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]

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 are: 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.

Hi 10SNUT,

I had to read this about 6 time until the light bulb went off!!!!

Correct me if I'm wrong but I think there is only one form involved here
and it is named "Form1". If you want to add a "New Contractor" you are
recursively calling "Form1" again.

Forgive me if I am conveying information you already know, but there is
such a long turn around time between postings that I want to pass as much
information with each post as possible.

ComboBoxes are for looking up existing data or in our case providing both
the look up feature along with the ability to add additional records. In
either case the "AfterUpdate" event of this ComboBox would have code to
move the record pointer to the record containing the field that matches
what we just selected.
----------------------------------------------------
Something like this UNTESTED AirCode:
Private Sub Contractor_AfterUpdate()

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0)
If Not Me.RecordsetClone.EOF Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
----------------------------------------------------

Normally you would use a ComboBox to place an ID number of some sort in
another table. In our example let's say we were issuing a purchase order
for some work to be accomplished. The form is bound to our WorkOrder
table. The Contractor table "tblContractors" describes each contractor and
has a ContractorID field that is a AutoNumber field. We have fields on our
form that completely describe the work to be accomplished along with a
ComboBox to select the Contractor.

Without going into too much detail here (which I would be glad to do if you
wish to go there) we set up our ComboBox the same way as we have in your
case. If we type in a completely new contractor and we want to add them to
our system, we are taken to a completely new form where we can define this
contractor properly and this contractor is added to the "tblContractors"
table. When we get back from adding the contractor the bound column of
this ComboBox will put the ContractoID number in our WorkOrder![Contractor]
field.

I hope I haven't bored you to death with stuff you already knew. If I
have, maybe someone else reading this thread didn't look at ComboBoxes this
way and found some useful information.

Does any of this make any sense to you? Am I on the right track as to how
your form is working?

Post back and I'll adjust direction as needed.

 

[Guest]

Hi RuralGuy, Hope you had a good weekend -
Okay, let's see if I can clear up a couple of the ambiguties I have created
in my previous explanation.
There is only one form involved. It is derived from the table "Table1
which has five fields in it (Field 1 through Field4) and the ContractorName
field which is also in Table1. As I mentioned in my previous post, the "Row
Source" for the ContractorName field on the form is "SELECT
[tblContractors].[ContractorName] FROM tblContractors;". Also the RowSource
Type is Table/Query. Is this a correct reference for this field?

I may not be doing this properly, but I have done it this way before. (the
reference from the 2 tables to create a form). I have the relationship for
the 2 tables linked with the ContractorName field.
In looking over your suggestion, it looks like I will have to add another
field to the tblContractors and that would be an AutoNumber field. This will
be called the Contractor ID field. There may be 10 contractors, and they
would have an ID of 1-10. If a new contractor is called for, we would be
sent to another form to enter the new ContractorName, and the updated ID
field would be self generated by the AutoNumber. Upon exit from entering the
new ContractorName into the tblContractors, I would return to the Form1, and
the new ContractorName would now be in the tblContractors so the error would
not occur. I assume that I would return back to the ContractorName field on
Form1. ??
To clarify a couple things I'm not sure of, to get to the new form to enter
the new ContractorName, I could have a macro on the NotInList property that
would open the new form to add the new ContractorName. After entering the
new name, then I could have an "exit" button on this form that would take me
back to Form1, and position me at the field to enter the requested
ContractorName, which would now be there because we just put it in the table.
Would I have to do any kind of "reset" to get past the error I got earlier
when the ContractorName I was asking for was not there?
You mentioned putting the Contractor ID number in the
WorkOrder![Contractor]. Why would we put the contractor ID in the work
Order, and not the contractor Name? In fact, why do we need a Contractoir ID
in the first place? Can't we just add the new ContractorName to the
tblContractors (while we're at the form that accepts the new Contractor
names) so it will be recognized when we return to Form1 to re-enter the new
ContractorName? Please explain the need for the Contractor ID.
Please have patience with my limitations, because I would hate to lose your
input.

10SNUT



10SNUT

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 are: 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.

Hi 10SNUT,

I had to read this about 6 time until the light bulb went off!!!!

Correct me if I'm wrong but I think there is only one form involved here
and it is named "Form1". If you want to add a "New Contractor" you are
recursively calling "Form1" again.

Forgive me if I am conveying information you already know, but there is
such a long turn around time between postings that I want to pass as much
information with each post as possible.

ComboBoxes are for looking up existing data or in our case providing both
the look up feature along with the ability to add additional records. In
either case the "AfterUpdate" event of this ComboBox would have code to
move the record pointer to the record containing the field that matches
what we just selected.
----------------------------------------------------
Something like this UNTESTED AirCode:
Private Sub Contractor_AfterUpdate()

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0)
If Not Me.RecordsetClone.EOF Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
----------------------------------------------------

Normally you would use a ComboBox to place an ID number of some sort in
another table. In our example let's say we were issuing a purchase order
for some work to be accomplished. The form is bound to our WorkOrder
table. The Contractor table "tblContractors" describes each contractor and
has a ContractorID field that is a AutoNumber field. We have fields on our
form that completely describe the work to be accomplished along with a
ComboBox to select the Contractor.

Without going into too much detail here (which I would be glad to do if you
wish to go there) we set up our ComboBox the same way as we have in your
case. If we type in a completely new contractor and we want to add them to
our system, we are taken to a completely new form where we can define this
contractor properly and this contractor is added to the "tblContractors"
table. When we get back from adding the contractor the bound column of
this ComboBox will put the ContractoID number in our WorkOrder![Contractor]
field.

I hope I haven't bored you to death with stuff you already knew. If I
have, maybe someone else reading this thread didn't look at ComboBoxes this
way and found some useful information.

Does any of this make any sense to you? Am I on the right track as to how
your form is working?

Post back and I'll adjust direction as needed.

--
RuralGuy

Please reply to the newsgroup so all may benefit.

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.

Hi 10SNUT,

I had to read this about 6 time until the light bulb went off!!!!

Correct me if I'm wrong but I think there is only one form involved here
and it is named "Form1". If you want to add a "New Contractor" you are
recursively calling "Form1" again.

Forgive me if I am conveying information you already know, but there is
such a long turn around time between postings that I want to pass as much
information with each post as possible.

ComboBoxes are for looking up existing data or in our case providing both
the look up feature along with the ability to add additional records. In
either case the "AfterUpdate" event of this ComboBox would have code to
move the record pointer to the record containing the field that matches
what we just selected.
----------------------------------------------------
Something like this UNTESTED AirCode:
Private Sub Contractor_AfterUpdate()

DoCmd.Requery ' Get any changes to the table first.

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0)
If Not Me.RecordsetClone.EOF Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
----------------------------------------------------

Normally you would use a ComboBox to place an ID number of some sort in
another table. In our example let's say we were issuing a purchase order
for some work to be accomplished. The form is bound to our WorkOrder
table. The Contractor table "tblContractors" describes each contractor and
has a ContractorID field that is a AutoNumber field. We have fields on our
form that completely describe the work to be accomplished along with a
ComboBox to select the Contractor.

Without going into too much detail here (which I would be glad to do if you
wish to go there) we set up our ComboBox the same way as we have in your
case. If we type in a completely new contractor and we want to add them to
our system, we are taken to a completely new form where we can define this
contractor properly and this contractor is added to the "tblContractors"
table. When we get back from adding the contractor the bound column of
this ComboBox will put the ContractoID number in our WorkOrder![Contractor]
field.

I hope I haven't bored you to death with stuff you already knew. If I
have, maybe someone else reading this thread didn't look at ComboBoxes this
way and found some useful information.

Does any of this make any sense to you? Am I on the right track as to how
your form is working?

Post back and I'll adjust direction as needed.

 

[RuralGuy]

Hi 10SNUT,

See responses in line...

Hi RuralGuy, Hope you had a good weekend -

Yes, very nice thank you!

Okay, let's see if I can clear up a couple of the ambiguties I have
created in my previous explanation.
There is only one form involved. It is derived from the table
"Table1
which has five fields in it (Field 1 through Field4) and the
ContractorName field which is also in Table1. As I mentioned in my
previous post, the "Row Source" for the ContractorName field on the
form is "SELECT [tblContractors].[ContractorName] FROM
tblContractors;". Also the RowSource Type is Table/Query. Is this a
correct reference for this field?

Yes, if you want to use a ComboBox.

I may not be doing this properly, but I have done it this way before.
(the reference from the 2 tables to create a form). I have the
relationship for the 2 tables linked with the ContractorName field.

What 2 tables? What is the other table used for and what does it look
like? Now would be a good time to describe the logical function of this
table. Is this just a test table to work out the details of a form?
What are the functions of Field1 to Field4? What is the function of this
"Form1"?

In looking over your suggestion, it looks like I will have to add
another
field to the tblContractors and that would be an AutoNumber field.
This will be called the Contractor ID field. There may be 10
contractors, and they would have an ID of 1-10. If a new contractor
is called for, we would be sent to another form to enter the new
ContractorName, and the updated ID field would be self generated by
the AutoNumber. Upon exit from entering the new ContractorName into
the tblContractors, I would return to the Form1, and the new
ContractorName would now be in the tblContractors so the error would
not occur. I assume that I would return back to the ContractorName
field on Form1. ??

To clarify a couple things I'm not sure of, to get to the new form to
enter the new ContractorName, I could have a macro on the NotInList
property that would open the new form to add the new ContractorName.

No macro, you already have the code to open a new form to properly
identify the new contractor. It is:

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

The only problem is you should probably open "Form2" rather than "Form1".
"Form2" would properly identify your Contractor with any information
needed such as phone number, address, contractor license number and so
forth. The form would be in add mode and simply closing the form would
save all of the information and return you to "Form1" right after the
DoCmd.OpenForm line.

After entering the new name, then I could have an "exit" button on
this form that would take me back to Form1, and position me at the
field to enter the requested ContractorName, which would now be there
because we just put it in the table.

That is correct!

Would I have to do any kind of "reset" to get past the error I got
earlier
when the ContractorName I was asking for was not there?
Nope!

You mentioned putting the Contractor ID number in the
WorkOrder![Contractor]. Why would we put the contractor ID in the
work Order, and not the contractor Name? In fact, why do we need a
Contractoir ID in the first place? Can't we just add the new
ContractorName to the tblContractors (while we're at the form that
accepts the new Contractor names) so it will be recognized when we
return to Form1 to re-enter the new ContractorName? Please explain the
need for the Contractor ID. Please have patience with my limitations,
because I would hate to lose your input.
10SNUT

Names are real lousy ID fields. "Bill's Plumbing Service" could change
it's name to "Bill and Sons Plumbing Service". "Sally Server" could get
married. In a properly normalized database, no information exists in
more than one location except for the "ID" fields which are usually
AutoNumber fields that the user *NEVER* sees.

I believe we are getting closer. Answering the above questions will take
us another step.

 

[Guest]

Hi again,
Thanks for the "reliable" response.

Here's the responses to your questions:
1. Your first comment was the settings I had in the properties for the
combo box was correct "if I wanted to use a Combo Box". What options are
available to acquire data from entry in a form that will update a table other
than a Combo Box? If there is a "simpler way" to accomplish this, I'm open
to it.

2. The functions of the 2 tables are:
tblContractors - Holds the names of all the contractors that are available
when entering into that field through Form1, as well as supporting info such
as address, phone, etc.
Table1 - Field1 thru Field 4 are just repositories to hold data which will
be used later to create reports, etc. The procedure we are using here is
only a simplified version of a larger procedure which will have more fields
in it. In short, Table1 will be used in the final procedure, although the
name will be different.

I'll try to use your suggestion adding Form2 as an input to the
tblContractors table. This makes good sense to me. It's hard to get away
from years of "macro programming", and enter into the foggy world of VBA
coding. Hopefully this exercise will convert me permanently.

I see your reasoning concerning the ID fields as lookup pointers to select
the ContractorName requested, but when the user gets to the point where they
need to enter a contractor, they type the name of the contractor, and as soon
as it recognizes the data being typed, the "look ahead" feature displays the
existing entry in the table, at which time they can tab to accept that entry.
When it's a new entry, the procedure we are creating here will accomplish
the task of adding the new entry to the tblContractors table. But what if
it's an original Contractor, but with a new or modified name? Will it make a
"new" entry into the tblContractors for the new name, but the old original
name will stay in the table? You could possible have "a few" different
entries for the same contractor in the table depending on what the user
enters. If it's a slight modification from the other entries for the same
contractor, it goes in as a new contractor. Is this correct?

I will add Form2 for data entry to the tblContractors, and see if the code I
enter is correct. I'll let you know on my next post how this went.

Thanks again for leading me through this darkness to a solution.

10SNUT

--
10SNUT

Hi 10SNUT,

See responses in line...

Hi RuralGuy, Hope you had a good weekend -

Yes, very nice thank you!

Okay, let's see if I can clear up a couple of the ambiguties I have
created in my previous explanation.
There is only one form involved. It is derived from the table
"Table1
which has five fields in it (Field 1 through Field4) and the
ContractorName field which is also in Table1. As I mentioned in my
previous post, the "Row Source" for the ContractorName field on the
form is "SELECT [tblContractors].[ContractorName] FROM
tblContractors;". Also the RowSource Type is Table/Query. Is this a
correct reference for this field?

Yes, if you want to use a ComboBox.

I may not be doing this properly, but I have done it this way before.
(the reference from the 2 tables to create a form). I have the
relationship for the 2 tables linked with the ContractorName field.

What 2 tables? What is the other table used for and what does it look
like? Now would be a good time to describe the logical function of this
table. Is this just a test table to work out the details of a form?
What are the functions of Field1 to Field4? What is the function of this
"Form1"?

In looking over your suggestion, it looks like I will have to add
another
field to the tblContractors and that would be an AutoNumber field.
This will be called the Contractor ID field. There may be 10
contractors, and they would have an ID of 1-10. If a new contractor
is called for, we would be sent to another form to enter the new
ContractorName, and the updated ID field would be self generated by
the AutoNumber. Upon exit from entering the new ContractorName into
the tblContractors, I would return to the Form1, and the new
ContractorName would now be in the tblContractors so the error would
not occur. I assume that I would return back to the ContractorName
field on Form1. ??

To clarify a couple things I'm not sure of, to get to the new form to
enter the new ContractorName, I could have a macro on the NotInList
property that would open the new form to add the new ContractorName.

No macro, you already have the code to open a new form to properly
identify the new contractor. It is:

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

The only problem is you should probably open "Form2" rather than "Form1".
"Form2" would properly identify your Contractor with any information
needed such as phone number, address, contractor license number and so
forth. The form would be in add mode and simply closing the form would
save all of the information and return you to "Form1" right after the
DoCmd.OpenForm line.

After entering the new name, then I could have an "exit" button on
this form that would take me back to Form1, and position me at the
field to enter the requested ContractorName, which would now be there
because we just put it in the table.

That is correct!

Would I have to do any kind of "reset" to get past the error I got
earlier
when the ContractorName I was asking for was not there?
Nope!

You mentioned putting the Contractor ID number in the
WorkOrder![Contractor]. Why would we put the contractor ID in the
work Order, and not the contractor Name? In fact, why do we need a
Contractoir ID in the first place? Can't we just add the new
ContractorName to the tblContractors (while we're at the form that
accepts the new Contractor names) so it will be recognized when we
return to Form1 to re-enter the new ContractorName? Please explain the
need for the Contractor ID. Please have patience with my limitations,
because I would hate to lose your input.
10SNUT

Names are real lousy ID fields. "Bill's Plumbing Service" could change
it's name to "Bill and Sons Plumbing Service". "Sally Server" could get
married. In a properly normalized database, no information exists in
more than one location except for the "ID" fields which are usually
AutoNumber fields that the user *NEVER* sees.

I believe we are getting closer. Answering the above questions will take
us another step.

 

[RuralGuy]

Good morning 10SNUT,

See responses in line.

Hi again,
Thanks for the "reliable" response.

Here's the responses to your questions:
1. Your first comment was the settings I had in the properties for
the combo box was correct "if I wanted to use a Combo Box". What
options are available to acquire data from entry in a form that will
update a table other than a Combo Box? If there is a "simpler way" to
accomplish this, I'm open to it.

I think for our purposes the ComboBox is the correct control to use.

2. The functions of the 2 tables are:
tblContractors - Holds the names of all the contractors that are
available when entering into that field through Form1, as well as
supporting info such as address, phone, etc.
Table1 - Field1 thru Field 4 are just repositories to hold data which
will be used later to create reports, etc. The procedure we are using
here is only a simplified version of a larger procedure which will
have more fields in it. In short, Table1 will be used in the final
procedure, although the name will be different.

What relationship does table1 have with tblContractors? Could there be
many records in table1 with Contractor1 in the Contractor Field? In other
words does Table1 have a Many-To-One relationship with tblContractors?

I'll try to use your suggestion adding Form2 as an input to the
tblContractors table. This makes good sense to me. It's hard to get
away from years of "macro programming", and enter into the foggy
world of VBA coding. Hopefully this exercise will convert me
permanently.

I see your reasoning concerning the ID fields as lookup pointers to
select the ContractorName requested, but when the user gets to the
point where they need to enter a contractor, they type the name of the
contractor, and as soon as it recognizes the data being typed, the
"look ahead" feature displays the existing entry in the table, at
which time they can tab to accept that entry.
When it's a new entry, the procedure we are creating here will
accomplish
the task of adding the new entry to the tblContractors table. But
what if it's an original Contractor, but with a new or modified name?
Will it make a "new" entry into the tblContractors for the new name,
but the old original name will stay in the table? You could possible
have "a few" different entries for the same contractor in the table
depending on what the user enters. If it's a slight modification from
the other entries for the same contractor, it goes in as a new
contractor. Is this correct?

Yup! That's why we intercept the "New" contractor and ask the user if they
want to create this new contractor. If the user is familiar with the
operation they will say "No, we've used them before. I must have typed
something different." They will say no to the question and be left ready
to pick a contractor. This time they will be more careful and scroll in
the selections to try to locate the contractor. Hopefully they will
succeed. Remember we are only recording the ContractorID field in Table1.
Any report or form that displays the contractor name will get the name from
the tblContractor table.

This brings up table maintenance. To properly maintain the contractor
table you really need a form (Form3? or modified Form2?) bound to
tblContractors with a means to navigate the table (navigation buttons or
another ComboBox). There should probably be TextBox controls on the form
bound to each field in the table. Once on a particular Contractor's
record, any change will modify the record including the Contractor name.
To complicate matters a little more, the maintenance form should have the
ability to "Delete" a Contractor and you should have Referential Integrity
turned on in case this ContractorID has been used elsewhere. Have I lost
you yet?

Do not use the "Look up a contractor" function in Form1 as the only means
to add to the Contractor table. The maintenance form should have the
ability to add a new contractor.

I will add Form2 for data entry to the tblContractors, and see if the
code I enter is correct. I'll let you know on my next post how this
went.

Thanks again for leading me through this darkness to a solution.

10SNUT

Well that's about it for this go around. Looking forward to how you made
out with "Form2".

 

[Guest]

Guess what RuralGuy !!!!
By adding Form2 which refers to tblContractors data, the functions work
correctly.

The way it functions now is:
Upon initiation, Form1 opens. Data is entered into the fields, and when the
user gets to the Contractor field, they try to enter the name of the
Contractor they are looking for. If there is one already in the
tblContractors table, then it is shown, and the user can accept that
Contractor and continue on to finish the process. If the Contractor they
want IS NOT there, then Form2 pops up, and there are entry fields to add a
new Contractor and his extended information (address, city,etc.) to
tblContractors. After the information has been completely added, then Form2
is closed, and you are returned back to Form1.

I only have one problem left, and this should be easy..... (for you)
After the data fields in Form2 are completed, I would like Form2 to close as
soon as the last field is tabbed OUT OF. I have put a button on Form2, and
after the last field is completed, I can click the button, and I can get
Form2 to close that way. That is cumberson, so I need to find a way to get
Form2 to close AFTER the entry is made in the last field and the TAB key is
pressed.

For argument's purpose, lets call the last field PHONE. I have put together
a code that should close Form2 when we exit the PHONE field, but I can't
determine what attribute to attach the procedure to. I have tried: After
Update, Lost Focus, Exit, and a couple others.

The code I want to use is:
Private Sub Phone_AfterUpdate()

'------------------------------------------------------------
' Form2_Close
'
'------------------------------------------------------------
Function Form2_Close()
On Error GoTo Form2_Close_Err

DoCmd.Close acForm, "Form2"


Form2_Close_Exit:
Exit Function

Form2_Close_Err:
MsgBox Error$
Resume Form2_Close_Exit

End Sub

Do you see anything obviously wrong with this code? I even put in a simple
macro to close Form2 after update, and it fails also. HOWEVER, the macro
does work if I just open Form 2, and enter data to the last field, PHONE, and
when I tab out of PHONE, Form2 closes the way I want it to. It just won't do
it while running in the procedure.

So that's my only problem left to tackle. Close Form2 when the last field
has been updated, and you tab out of it. You're doing GREAT. Any ideas on
this final fix?

10SNUT

 

[RuralGuy]

Guess what RuralGuy !!!!
By adding Form2 which refers to tblContractors data, the functions
work correctly.

The way it functions now is:
Upon initiation, Form1 opens. Data is entered into the fields, and
when the user gets to the Contractor field, they try to enter the name
of the Contractor they are looking for. If there is one already in
the tblContractors table, then it is shown, and the user can accept
that Contractor and continue on to finish the process. If the
Contractor they want IS NOT there, then Form2 pops up, and there are
entry fields to add a new Contractor and his extended information
(address, city,etc.) to tblContractors. After the information has
been completely added, then Form2 is closed, and you are returned back
to Form1.

I only have one problem left, and this should be easy..... (for you)
After the data fields in Form2 are completed, I would like Form2 to
close as soon as the last field is tabbed OUT OF. I have put a button
on Form2, and after the last field is completed, I can click the
button, and I can get Form2 to close that way. That is cumberson, so
I need to find a way to get Form2 to close AFTER the entry is made in
the last field and the TAB key is pressed.

For argument's purpose, lets call the last field PHONE. I have put
together a code that should close Form2 when we exit the PHONE field,
but I can't determine what attribute to attach the procedure to. I
have tried: After Update, Lost Focus, Exit, and a couple others.

The code I want to use is:
Private Sub Phone_AfterUpdate()

'------------------------------------------------------------
' Form2_Close
'
'------------------------------------------------------------
Function Form2_Close()
On Error GoTo Form2_Close_Err

DoCmd.Close acForm, "Form2"


Form2_Close_Exit:
Exit Function

Form2_Close_Err:
MsgBox Error$
Resume Form2_Close_Exit

End Sub

Do you see anything obviously wrong with this code? I even put in a
simple macro to close Form2 after update, and it fails also. HOWEVER,
the macro does work if I just open Form 2, and enter data to the last
field, PHONE, and when I tab out of PHONE, Form2 closes the way I want
it to. It just won't do it while running in the procedure.

So that's my only problem left to tackle. Close Form2 when the last
field has been updated, and you tab out of it. You're doing GREAT.
Any ideas on this final fix?

10SNUT

You should be able to use the Phone_AfterUpdate event which will insist
they put something in the phone number, otherwise the event never fires.
Even though I don't like running forms this way my preference would be the
Phone_LostFocus event. This is all you should need:


Private Sub Phone_LostFocus()

DoCmd.Close acForm, Me.Name, acSaveNo

End Sub

Note: The acSaveNo is for the form, *not* the data!

My preference would be to make tabbing out of the Phone field go to the
ExitButton you spoke of. Pressing <Enter> while on the button "Pushes" the
button. Viola!! we're back in Form1.

You may also want to make Form2 cycle on the Current Record. It is on the
"Other" tab for the Form.

hth

 

[Guest]

TGIF RuralGuy,
I was mistaken in my last post to you about what is really happening. For
the most part, I was correct, but I need to correct a couple things:

After I pass through Form1 entering data in all the fields, and I get to the
Contractor entry, if I enter a new contractor, it asks me if I want to add
the new contractor to the list. I respond "yes", and it takes me to Form2,
and this is a 6 field form to enter Contractor information. After I get to
the last field, "Phone", I tab out to the "Exit" button. From this point on,
what I told you is different. Here's what really happens:
When positioned on the "Exit" button, I can either hit <Enter>, or click on
the "Exit" button, and it always goes to field 1 of Form2 AGAIN. I have to
enter data for a another Contractor, and it has to be different because it
recognizes the Contractor I entered on the 1st pass. When I finish entering
data for the 2nd Contractor, it positions me over the "Exit" button, at which
time I can click on the button, or hit the <Enter> key. Then it closes
Form2, and goes back to Form1 the way it should.

Here's an interesting "quirk" I just found out:
If, after the 1st pass, when it leaves the "Exit" button to go back to the
1st field of Form2, THEN if I click on the "Exit" button, it closes Form2,
and goes back to Form1.

I know it's simply a matter of the code I have entered, but I even tried
actual macro entries, and got the same results. I'm at a lost as to what to
do next. I'll keep playing around with it to see what I can get. The last
issue I discovered about clicking on <Enter> as it enters Form2 for the 2nd
pass, and that gets me back to Form1, is a new possibility. I'll see what I
can do there.

I asked this before, but is there any way I can email this routine to you
without disclosing email addresses to the world? It's small, and it would
save us both a lot of time.

Don't get me wrong, I am learning a LOT with what we're doing now. I just
wanted to know if this is something you might be interested in.

Thanks again,
10SNUT
10SNUT

 

[Guest]

RuralGuy,

I found the problem. Everything works perfectly now.

On the original procedure that we used to exit Form1 to Form2 when a
Contractor needed to be added, I had (somehow??) coded the "Open Form2"
statement TWICE. Even though it didn't look like we were in that area, when I
commented out that second Open "Form2" argument, everything works good.

I want to thank you VERY MUCH for your patience with me and my problem. I
will hope to get you as a "respondent" to any future postings I submit to the
Access
Discussion Group.

Relieved and Satisfied,
10SNUT

 

[RuralGuy]

RuralGuy,

I found the problem. Everything works perfectly now.

On the original procedure that we used to exit Form1 to Form2 when a
Contractor needed to be added, I had (somehow??) coded the "Open
Form2" statement TWICE. Even though it didn't look like we were in
that area, when I commented out that second Open "Form2" argument,
everything works good.

I want to thank you VERY MUCH for your patience with me and my
problem. I will hope to get you as a "respondent" to any future
postings I submit to the Access
Discussion Group.

Relieved and Satisfied,
10SNUT

Hi 10SNUT,

Outstanding!!! Best news I've had all day. Really glad I was able to
assist you in your success. Now you can pass it on to someone else!