Adding records using combo boxes in a form

[Guest]

Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:

Dim strAQL As String
Dim i As Integer
Dim Msg As String

' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub

I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.

In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!

I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!

I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.

Owl Lover

 

[Andrew]

Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:

Dim strAQL As String
Dim i As Integer
Dim Msg As String

' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub

I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.

In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!

I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!

I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.

Owl Lover

Hi

The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.

The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!

I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.

So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has 2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?

I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.

---

Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)

On Error Goto ErrHandle

Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text

intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)

If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If

ExitHere:
Exit Sub

ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere


End Sub

 

[Guest]

Thank you very much for your response. I work on this database in my spare
time as I have a full time job. So I will try your code tonight when I get
home and hopefully that will solve my problem. To be perfectly honest, I
think I'm in over my head with this database! When it comes to writing code,
I know what I want to do but I'm not sure how to write it down! I worked
with Access about 4 years ago and haven't touched it since. So I feel like
I've lost more than I ever knew! But a friend of mine works for a company
who wanted to transfer their Approach database into Access, and they offered
me the job. It started out pretty straight forward, but since then they have
changed and changed and .... well, you get the picture! So now, I really
wonder if I made a mistake taking this on. Thank goodness I have this
resource, because this website has been invaluable to me. Anyway, for now
I'll keep plugging along. I'll let you know how the code works.

Again, thank you for your help and your explanation!!

Rosemary (Owl Lover)

Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:

Dim strAQL As String
Dim i As Integer
Dim Msg As String

' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub

I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.

In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!

I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!

I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.

Owl Lover

Hi

The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.

The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!

I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.

So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has 2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?

I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.

---

Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)

On Error Goto ErrHandle

Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text

intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)

If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If

ExitHere:
Exit Sub

ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere


End Sub

 

[Guest]

Hi Andrew:

I just finished entering your code below, and it comes up with an error
message: "Compile error: SYNTAX error" on the following:

Values ('" & NewData _
& "' , ' & strNewDescription & "')"

I cut and pasted your code so it would enter correctly. I'm still going
over it to see if I need to change something, but I would appreciate your
input.

Thanks for your help!

Rosemary (Owl Lover)

Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:

Dim strAQL As String
Dim i As Integer
Dim Msg As String

' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub

I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.

In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!

I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!

I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.

Owl Lover

Hi

The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.

The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!

I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.

So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has 2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?

I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.

---

Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)

On Error Goto ErrHandle

Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text

intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)

If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If

ExitHere:
Exit Sub

ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere


End Sub

 

[Andrew]

Hi Andrew:

I just finished entering your code below, and it comes up with an error
message: "Compile error: SYNTAX error" on the following:

Values ('" & NewData _
& "' , ' & strNewDescription & "')"

I cut and pasted your code so it would enter correctly. I'm still going
over it to see if I need to change something, but I would appreciate your
input.

Thanks for your help!

Rosemary (Owl Lover)

Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:
Dim strAQL As String
Dim i As Integer
Dim Msg As String
' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub
I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.
In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!
I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!
I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.
Owl Lover

Hi

The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.

The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!

I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.

So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has 2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?

I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.

Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)

On Error Goto ErrHandle

Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text

intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)

If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If

ExitHere:
Exit Sub

ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere

End Sub- Hide quoted text -

- Show quoted text -

Hi

Sorry for taking a couple days to come back to you - I was away from
my office for a few days.

The problems with the line you highlighted are twofold:
1) I'm guessing that there may well be a problem with the line
breaks... Need to make sure that any time there is a line break you
have an underscore before the break, and if the break is within a
string, close the string, use an ampersand and an underscore and then
re-start the string.
2) Forgot an ampersand in my own code! Sorry - I shouldn't have tried
to shortcut things and type code directly into here without testing it
first.

So, here's the re-designed code with very short lines!:

Private Sub cboPartNumber_NotInList _
(NewData As String, Response As Integer)


On Error GoTo ErrHandle

'For answers to MsgBoxes and InputBoxes
Dim intResponse As Integer, strNewDescription As String
'To be used for populating the table with the new values
Dim cmd As ADODB.Command
Dim strSQL As String 'For the Command Text


intResponse = MsgBox(NewData & _
" is not in the current list of Part Numbers." _
& vbNewLine & "Do you want to add it", _
vbQuestion + vbYesNo)


If intResponse = vbNo Then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = _
InputBox("Please enter a description for the Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription) " _
& "Values ('" & NewData & "' , '" & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End With
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If


ExitHere:
Exit Sub


ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere


End Sub

 

[Guest]

Hi Andrew:

Thanks for getting back to me. Glad you were able to get away from work!
We all need it once and awhile. I worked from home on Friday so it gave me
time to get some other things done with the database. I'm at work right now,
so I'll try this code when I get home. I really appreciate all the help you
have given me. I've run into another problem with information in a report,
but I need to get with the person who will be using this database before I
can post my question. But it deals with calculated fields within a report,
so I will post the question as soon as I hear from her.

I would like to ask you another question, though. How important is it, when
establishing relationships, to designate them as one-to-many, one-to-one,
etc.? That has always been a problem with me and when I set up the
relationships in the tables I just set up the relationships and didn't
designate them. Is that going to be a problem? I have this gut feeling that
I've done everything wrong and it's probably going to come back to bite me
later! I took beginning, intermediate and advanced classes in Access about 5
years ago, but because it's been so long since I've worked with it I've lost
a lot. I feel like I'm starting all over again, and I'm afraid I've set it
up all wrong. This input form is as far as I've gone except to "play" with 1
report, which is the one that I will have the questions on. I guess if I
have to go back to the beginning and start over again I could, but I'm not
sure if the company I'm building this for would be too happy about that. So,
the question again is, should I go back and designate the relationships?

Thanks again for your response and I'll hopefully hear from you soon.

Rosemary (Owl Lover)

Hi Andrew:

I just finished entering your code below, and it comes up with an error
message: "Compile error: SYNTAX error" on the following:

Values ('" & NewData _
& "' , ' & strNewDescription & "')"

I cut and pasted your code so it would enter correctly. I'm still going
over it to see if I need to change something, but I would appreciate your
input.

Thanks for your help!

Rosemary (Owl Lover)

On Aug 30, 12:04 am, Owl Lover <[email protected]>
wrote:
Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:

Dim strAQL As String
Dim i As Integer
Dim Msg As String

' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub

I wrote the code based on an email that directed me to a webside, "Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I should pick one
from the list. Obviously I've done something wrong.

In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!

I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!

I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.

Owl Lover

The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.

The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!

I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.

So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has 2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?

I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.

Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)

On Error Goto ErrHandle

Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text

intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)

If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If

ExitHere:
Exit Sub

ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere

End Sub- Hide quoted text -

- Show quoted text -

Hi

Sorry for taking a couple days to come back to you - I was away from
my office for a few days.

The problems with the line you highlighted are twofold:
1) I'm guessing that there may well be a problem with the line
breaks... Need to make sure that any time there is a line break you
have an underscore before the break, and if the break is within a
string, close the string, use an ampersand and an underscore and then
re-start the string.
2) Forgot an ampersand in my own code! Sorry - I shouldn't have tried
to shortcut things and type code directly into here without testing it
first.

So, here's the re-designed code with very short lines!:

Private Sub cboPartNumber_NotInList _
(NewData As String, Response As Integer)


On Error GoTo ErrHandle

'For answers to MsgBoxes and InputBoxes
Dim intResponse As Integer, strNewDescription As String
'To be used for populating the table with the new values
Dim cmd As ADODB.Command
Dim strSQL As String 'For the Command Text


intResponse = MsgBox(NewData & _
" is not in the current list of Part Numbers." _
& vbNewLine & "Do you want to add it", _
vbQuestion + vbYesNo)


If intResponse = vbNo Then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = _
InputBox("Please enter a description for the Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription) " _
& "Values ('" & NewData & "' , '" & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End With
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If


ExitHere:
Exit Sub


ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere


End Sub

 

[Andrew]

Hi Andrew:

Thanks for getting back to me. Glad you were able to get away from work!
We all need it once and awhile. I worked from home on Friday so it gave me
time to get some other things done with the database. I'm at work right now,
so I'll try this code when I get home. I really appreciate all the help you
have given me. I've run into another problem with information in a report,
but I need to get with the person who will be using this database before I
can post my question. But it deals with calculated fields within a report,
so I will post the question as soon as I hear from her.

I would like to ask you another question, though. How important is it, when
establishing relationships, to designate them as one-to-many, one-to-one,
etc.? That has always been a problem with me and when I set up the
relationships in the tables I just set up the relationships and didn't
designate them. Is that going to be a problem? I have this gut feeling that
I've done everything wrong and it's probably going to come back to bite me
later! I took beginning, intermediate and advanced classes in Access about 5
years ago, but because it's been so long since I've worked with it I've lost
a lot. I feel like I'm starting all over again, and I'm afraid I've set it
up all wrong. This input form is as far as I've gone except to "play" with 1
report, which is the one that I will have the questions on. I guess if I
have to go back to the beginning and start over again I could, but I'm not
sure if the company I'm building this for would be too happy about that. So,
the question again is, should I go back and designate the relationships?

Thanks again for your response and I'll hopefully hear from you soon.

Rosemary (Owl Lover)

Hi Andrew:
I just finished entering your code below, and it comes up with an error
message: "Compile error: SYNTAX error" on the following:
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
I cut and pasted your code so it would enter correctly. I'm still going
over it to see if I need to change something, but I would appreciate your
input.
Thanks for your help!
Rosemary (Owl Lover)
:
On Aug 30, 12:04 am, Owl Lover <[email protected]>
wrote:
Yesterday I asked the question regarding adding part numbers while in a form.
I tried the fix I got back but it's still not working properly. So I'm
asking again if anyone can help me. I've written the following code:
Dim strAQL As String
Dim i As Integer
Dim Msg As String
' Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i - vbYes Then
strSQL = "Insert Into tblPart# ([strPartNumber]) " & _
"Values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End Sub
I wrote the code based on an email that directed me to a webside,"Using the
NotInList event of a Combo Box" in Database Solutions for Microsoft Access,
which someone directed me to. I have a part number and a part description. I
need to give the user the ability to add new part numbers and in turn type in
the part description. Today, I received a response back saying the
following: "I think the correct response is acDataErrAdded in combination
with a requery." I'm assuming that means I should change both the Response =
acDataErrContinue and the else Response - acDataErrContinue to
acDataErrAdded, but I'm not sure, so I tried that. but when I changed both
of them, now it is giving me an error message saying it doesn't recognize
that part number because it is not part of the list, and I shouldpick one
from the list. Obviously I've done something wrong.
In addition to adding a part number I have to be able to tab to the next
field
which is the part description, and add the description as well, then tab out
of there
and have the part number and part description save to the table. It's
obvious I don't know what I'm doing, and I could really use some help!
I also don't understand what he meant when he told me to do a requery.
Having said that, I'm really not very knowledgable regarding writing code, so
I'm afraid I'm in over my head!
I'm hoping someone can assist me further as to how to proceed. I would
appreciate any help anyone can give me. I'm really not sure I'm making
myself understood but just let me know and I'll try to clear up any
confusion, if I can. Thank you in advance.
Owl Lover
Hi
The not in list event can certainly be used to populate a table with
currently unrecognised data - indeed that's why it's there. It has 2
arguments, NewData being the value the user typed which isn't
recognised, and Response which is your way of telling Access what to
do next. Your options for Response are as follows:
acDataErrContinue - This means that you have handled the error
yourself by telling the user what to do (or whatever) and you don't
need Access to display its standard error message.
acDataErrAdded - THis means that you have now added the new value into
the list of items behind this combo box, so Access should be able to
re-build that list and find the value represented by NewData. If it
tries to do this and still can't find the new item, you'll still get
the standard error message, as you described.
acDataErrDisplay - Tells Access to do it's thing and display the
standard error. This is what you get if you don't code this event at
all.
The main problem you're likely to have is that you are going to
trigger this code because the user entered an unrecognised part number
in the combo box, but you still want to make use of a text box (the
part description) which the user hasn't got to yet, because they've
been unable to leave the combo box, having entered an unrecognised
number!
I would suggest that one viable solution would be to respond to the
not in list event of the PartNumber combo by asking in an InputBox for
the description to go with the part number. Then you can populate both
fields with the data as part of your code.
So, here's some code that you'll need to adapt. I'm assuming that the
combo box is called cboPartNumber and that the table (tblPart#) has2
fields - strPartNumber and strDescription. Incidentally - you need to
be careful with naming conventions and data types - I take it that
strPartNumber is a Text data type from the str prefix, even though
it's called PartNumber?
I've also included some rudimentary error handling. Let me know if
there's anything that you're not sure of.
---
Private Sub cboPartNumber_NotInList(NewData as String, Response as
Integer)
On Error Goto ErrHandle
Dim intResponse as Integer, strNewDescription as String 'For answers
to MsgBoxes and InputBoxes
Dim cmd as ADODB.Command 'To be used for populating the table with the
new values
Dim strSQL as String 'For the Command Text
intResponse = MsgBox(NewData & " is not in the current list of Part
Numbers." _
& vbNewLine & "Do you want to add it", vbQuestion + vbYesNo)
If intResponse = vbNo then
'Confirm that user will need to use existing values
MsgBox "Please select an existing Part Number"
' Tell Access that we've handled it.
Response = acDataErrContinue
Else
strNewDescription = InputBox("Please enter a description for the
new Part Number")
'Build the SQL string to insert the data
strSQL = "Insert into tblPart# (strPartNumber, strDescription)
Values ('" & NewData _
& "' , ' & strNewDescription & "')"
' Set up new command object for inserting data
Set cmd = New ADODB.Command
with cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End with
Set cmd = Nothing
'Tell Access that we've added the data and it should be available
Response = acDataErrAdded
End If
ExitHere:
Exit Sub
ErrHandle:
MsgBox "An error occurred, with the following details: " & vbNewLine _
& "Error number: " & Err.Number & vbNewLine _
& "Error message: " & Err.Description, vbExclamation
Resume ExitHere
End Sub- Hide quoted text -
- Show quoted text -

Hi

Sorry for taking a couple days to come back to you - I was away from
my office for a few days.

The problems with the line you highlighted are twofold:
1) I'm guessing that there may well be a problem with the line
breaks... Need to make sure that any time there is a line break you
have an underscore before the break, and if the break is within a
string, close the string, use an ampersand and an underscore and then
re-start the string.
2) Forgot an ampersand in my own code! Sorry - I shouldn't have tried
to shortcut things and type code directly into here without testing it
first.

So, here's the re-designed code with

...

read more »- Hide quoted text -

- Show quoted text -

Don't worry - I remember first getting to grips with Access. It can
seem a bit overwhelming at first!

There's good news and bad with regard to relationship types.
The good news - you don't need to worry. Access automatically
determines whether a relationship is one-to-one, one-to-many or many-
to-many (what Access calls "Indeterminate", and will not do much with,
as they are generally bad news...). So, you don't need to specify it
at all.

The bad news - in order to do this, Access relies on your tables being
set up right.
What does that mean? Well, each table must have a Primary Key. And if
you want to relate a table to this table (the "parent" table), then
the secondary (or "child") table must have a field which is used for
making that relationship work. This field is known as the Foreign Key,
and it must be of the same data type and field size as the Primary Key
in the parent table.

So, say you have a field called ID in a customers table, which is of a
data type Number and a field size of Long Integer. If you also have a
table called Orders which must relate to the Customers (those
customers being the same people who placed the orders, presumably)
then you must create a field in the Orders table, perhaps called
CustomerID, which will also be Number / Long Integer. For each order,
this will hold the ID value for the customer that placed the order.
Note that although it's common (and very good) practise to name the
fields the same (so CustomerID in both, rather than ID in the
customers table and CustomerID in the orders table), Access doesn't
actually require it.

Now, as long as you've followed the above rules, when you go to the
relationships window and create the relationship between the Primary
Key in the parent table and the Foreign Key in the orders table,
Access will figure out what's going on and create a one-to-many
relationship. If you look at the bottom of the Edit Relationships
dialogue box, it will even tell you that it's One-to-many. You should
also, when creating the relationship, tick the box labelled "Enforce
referential integrity", as a general rule. This will ensure that you
can't, for example, assign an order to customer 123 if there is no
such ID value in the customers table. It will also check out your
existing data, and refuse to create the relationship if you have
already got data which breaks this rule.

Hope this helps and makes sense!

Andrew

 

[Guest]

Hi Andrew,

Thanks for getting back to me. You are correct when you say that Access can
be overwhelming! You have no idea how overwhelmed I am!! My husband and I
celebrated our 30th wedding anniversary the first of the week and I am so
"overwhelmed" with this project that I had trouble enjoying myself! I'm also
losing sleep! Is that overwhelmed enough for you!!! Anyway, your
explanation about relationships makes perfect sense. My problem with all of
this is that I am a very visual person. I did very well in all the classes I
took for Access because I had a teacher there to show me. But to look things
up in a book or even try to put it into words on this website is a struggle
for me. I understand the principle but I'm not sure how to put it into words
or action. The fact that I took the classes over 5 years ago doesn't help!

Anyway, moving right along - I wrote the code you gave me and I'm still
having a problem. I think I know what it is so I'm going to try to figure it
out without crying on your (or someone elses) shoulder!! If I can't get it,
I'll be writing back! Thanks for all your help and I'll let you know if I
get it figured out.

Rosemary (Owl Lover)