Adding info through combo boxes

[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 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.
The problem is that this code allows me to type in a new part number, but
when I try to tab out of it to the part description field, it keeps asking me
if I want to add a new part number and won't let me out of that field, and
doesn't seem to save the part number I just typed in to the table. 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 that 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, but I could really use some help!

Any help will be much appreciated!

Owl Lover

 

[Pieter Wijnen]

I think the correct response is acDataErrAdded in combination with a requery

HTH
Pieter

 

[Owl Lover]

Thank you for your response. Unfortunately I'm not sure I understand how to
do a requery. 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. Having said that, I'm really
not very knowledgable regarding writing code, so I'm afraid I'm in over my
head! I appreciate your responding to my problem, but I'm hoping you can
assist me further as to how to proceed. When you say acDataErrAdded, should
I change both the Response = acDataErrContinue and the else Response -
acDataErrContinue to acDataErrAdded? I'm assuming that's what I need to do.
But I still don't understand about requery, so any help you can give me will
be very much appreciated!

Owl Lover

I think the correct response is acDataErrAdded in combination with a requery

HTH
Pieter

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 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.
The problem is that this code allows me to type in a new part number, but
when I try to tab out of it to the part description field, it keeps asking
me
if I want to add a new part number and won't let me out of that field, and
doesn't seem to save the part number I just typed in to the table. 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 that 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, but I could really use some help!

Any help will be much appreciated!

Owl Lover

 

[Beetle]

Hello again.

Sorry I didn't respond sooner, been working alot lately.

Right now I just have one question. Did you copy and paste your code from
your DB into your post? If so, you have a typo in your code that may be the
cause of your problem. Your first line, where you declare your string
variable, is;

Dim strAQL As String (it should be strSQL)

As a result, Access cannot execute strSQL, because it hasn't been declared,
therefore Access doesn't know what it is.

If this typo only exists in your post, and not in your code, post back and
we'll go from there.

HTH

Thank you for your response. Unfortunately I'm not sure I understand how to
do a requery. 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. Having said that, I'm really
not very knowledgable regarding writing code, so I'm afraid I'm in over my
head! I appreciate your responding to my problem, but I'm hoping you can
assist me further as to how to proceed. When you say acDataErrAdded, should
I change both the Response = acDataErrContinue and the else Response -
acDataErrContinue to acDataErrAdded? I'm assuming that's what I need to do.
But I still don't understand about requery, so any help you can give me will
be very much appreciated!

Owl Lover

I think the correct response is acDataErrAdded in combination with a requery

HTH
Pieter

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 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.
The problem is that this code allows me to type in a new part number, but
when I try to tab out of it to the part description field, it keeps asking
me
if I want to add a new part number and won't let me out of that field, and
doesn't seem to save the part number I just typed in to the table. 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 that 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, but I could really use some help!

Any help will be much appreciated!

Owl Lover

 

[Owl Lover]

Please, there is no need to apologize. This database is a part time job for
me as I already have a full time one! I'm beginning to think I've bitten off
more than I can chew!!

In answer to your question, yes, I did cut and paste. Thank you for
catching that. I will work on it when I get home tonight and hopefully that
will fix the problem. I will let you know.

Again, thank you,

Owl Lover

Hello again.

Sorry I didn't respond sooner, been working alot lately.

Right now I just have one question. Did you copy and paste your code from
your DB into your post? If so, you have a typo in your code that may be the
cause of your problem. Your first line, where you declare your string
variable, is;

Dim strAQL As String (it should be strSQL)

As a result, Access cannot execute strSQL, because it hasn't been declared,
therefore Access doesn't know what it is.

If this typo only exists in your post, and not in your code, post back and
we'll go from there.

HTH

Thank you for your response. Unfortunately I'm not sure I understand how to
do a requery. 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. Having said that, I'm really
not very knowledgable regarding writing code, so I'm afraid I'm in over my
head! I appreciate your responding to my problem, but I'm hoping you can
assist me further as to how to proceed. When you say acDataErrAdded, should
I change both the Response = acDataErrContinue and the else Response -
acDataErrContinue to acDataErrAdded? I'm assuming that's what I need to do.
But I still don't understand about requery, so any help you can give me will
be very much appreciated!

Owl Lover

I think the correct response is acDataErrAdded in combination with a requery

HTH
Pieter

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 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.
The problem is that this code allows me to type in a new part number, but
when I try to tab out of it to the part description field, it keeps asking
me
if I want to add a new part number and won't let me out of that field, and
doesn't seem to save the part number I just typed in to the table. 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 that 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, but I could really use some help!

Any help will be much appreciated!

Owl Lover

 

[John W. Vinson]

In answer to your question, yes, I did cut and paste. Thank you for
catching that. I will work on it when I get home tonight and hopefully that
will fix the problem. I will let you know.

Again, thank you,

Owl Lover

You can catch this kind of typo more easily by putting a line

Option Explicit

at the very top of the form's module (before the first Private Sub line). It's
good practice to do so routinely. It will catch any cases where you try to use
a variable which has not been declared.

John W. Vinson [MVP]

 

[Owl Lover]

Thank you John! I'll be sure to remember that piece of advice!

Rosemary (Owl Lover)