Using "Not in list" to add data to two fields

J

jlm722

Registered User.
Local time
Today, 15:15
Joined
Nov 6, 2008
Messages
42
Hey there forum friends, I have a dilemma. I have a table (let’s call it tblAcctNum). It has two fields, AcctNumber and AcctName. On one of my forms, I have a combo box that looks up numbers in the “AcctNumber” field. The main reason for this is many of the “AcctNumber”s are used quite frequently. I also have a dlookup function on my form that displays the “AcctName” field that is matched to the AcctNumber. I am familiar with how to set the “not in list” event procedure to add a new AcctNumber. My dilemma is, I want the “not in list” event to open a pop-up form that will allow me to enter BOTH new fields, then click a save button that will return me back to the original form with the new information added so I may continue. When the pop-up form pops up, I would want it to have the new AcctNumber I entered already filled in, with my focus on the AcctName field. (In other words, when I add a new number to the list, I need a way to put an AcctName with that new number) I really wouldn’t even mind if after adding the new number, it simply popped up and asked me to give it the name. What ever will seem to be the cleanest easiest way for this to work, Thanks in advance!!!
 
Last edited:
Could you show how you're currently handling the Not In List event?
 
actually, the only thing it is currently doing, is letting me add a number. The problem is, I don't know how to add to two different fields. And by the way, I'm using Access 2000, but i'll post the code if you want it, hold a sec
 
Private Sub Combo187_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo187_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Account Number " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Account Information")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblAcctNum([AcctNumber]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new account number has been added." _
, vbInformation, "Account Information"
Response = acDataErrAdded
Else
MsgBox "Please choose an account number from the list." _
, vbInformation, "Account Information"
Response = acDataErrContinue
End If
Combo187_NotInList_Exit:
Exit Sub
Combo187_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo187_NotInList_Exit
End Sub
 
Phew!! Could you put your code in code tags? :)

[ c o d e ]
Private Sub Combo187_NotInList(NewData As String, Response As Integer)

....

End Sub
[ / c o d e ]

Without the spaces. It helps for readability.
 
? huh? ya lost me. never heard of code tags.
 
attachment.php
 

Attachments

  • codetag.png
    codetag.png
    42.7 KB · Views: 303
Code: 
[FONT=Times New Roman]Private Sub Combo187_NotInList(NewData [COLOR=navy]As String[/COLOR], Response [COLOR=navy]As Integer[/COLOR])
    [COLOR=navy]On Error GoTo [/COLOR]Combo187_NotInList_Err
    [COLOR=navy]Dim [/COLOR]intAnswer [COLOR=navy]As Integer[/COLOR]
    [COLOR=navy]Dim [/COLOR]strSQL [COLOR=navy]As String[/COLOR]
    intAnswer = MsgBox("The Account Number " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Account Information")
    [COLOR=navy]If [/COLOR]intAnswer = vbYes [COLOR=navy]Then[/COLOR]
        strSQL = "INSERT INTO tblAcctNum([AcctNumber]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings [COLOR=navy]False[/COLOR]
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings [COLOR=navy]True[/COLOR]
        MsgBox "The new account number has been added." _
            , vbInformation, "Account Information"
        Response = acDataErrAdded
    [COLOR=navy]Else[/COLOR]
        MsgBox "Please choose an account number from the list." _
            , vbInformation, "Account Information"
        Response = acDataErrContinue
    [COLOR=navy]End If[/COLOR]
Combo187_NotInList_Exit:
    [COLOR=navy]Exit Sub[/COLOR]
Combo187_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    [COLOR=navy]Resume [/COLOR]Combo187_NotInList_Exit
[COLOR=navy]End Sub[/COLOR][/FONT]
 
It didn't work on your code :p

Anyhow,
1. Save the value of the new number into a variable before inserting.
2. Set that value to the value of the combo box after saving, just after the message box.

combo187.value = newValue_Variable
 
vbaInet, You folks are wonderful, and I know JUST enough about vba and access to be dangerous :o) But I am completely confused. What exactly is the response you just sent? combo187.value = newValue_Variable? what does that do? I think maybe I haven't explained what exactly I'm trying to accomplish
 
The code I'm using works fine to add a new number to my list. The problem is, I need to add to two fields with one "not in list" event.
 
Not sure if this is what you want but try it and see:

Code: 
[FONT=Times New Roman][FONT=Times New Roman]Private Sub Combo187_NotInList(NewData [COLOR=navy]As String[/COLOR], Response [COLOR=navy]As Integer[/COLOR])
    [COLOR=navy]On Error GoTo [/COLOR]Combo187_NotInList_Err
    [COLOR=navy]Dim [/COLOR]intAnswer [COLOR=navy]As Integer[/COLOR]
    [COLOR=navy]Dim [/COLOR]strSQL [COLOR=navy]As String[/COLOR]
    intAnswer = MsgBox("The Account Number " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Account Information")
    [COLOR=navy]If [/COLOR]intAnswer = vbYes [COLOR=navy]Then[/COLOR]
        strSQL = "INSERT INTO tblAcctNum([AcctNumber]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings [COLOR=navy]False[/COLOR]
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings [COLOR=navy]True[/COLOR]
        MsgBox "The new account number has been added." _
            , vbInformation, "Account Information"
[COLOR=Red][B]       Combo187.value = NewData[/B][/COLOR]
        Response = acDataErrAdded
    [COLOR=navy]Else[/COLOR]
        MsgBox "Please choose an account number from the list." _
            , vbInformation, "Account Information"
        Response = acDataErrContinue
    [COLOR=navy]End If[/COLOR]
Combo187_NotInList_Exit:
    [COLOR=navy]Exit Sub[/COLOR]
Combo187_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    [COLOR=navy]Resume [/COLOR]Combo187_NotInList_Exit
[COLOR=navy]End Sub
[/COLOR][/FONT][/FONT]
 
jlm722 said:
The code I'm using works fine to add a new number to my list. The problem is, I need to add to two fields with one "not in list" event.
Click to expand...
Hmm... elaborate on this. Add what to what fields?
 
sorry, you have to pardon my explanations sometimes. My table has two fields. one is for an account number, the other is for the name associated with the account number. This is an application I am using to cash customers payroll checks. There is a field on my form to enter the payor of a check. Every employee I have enters the payors name differently. ie: D&E Trucking, D and E Trucking, DE Trucking, D.E. Trucking and so on and so on. I am trying to create a table, where the payor's bank account number is stored, along with the payors's name. I have no problem creating a "not in list" event that lets me store new account number, the problem is, I need to be able to also add the payor's name also! So what I'm needing is, once the combo box/table accepts the new account number, it needs to prompt me for the name also. Is ANY of this making sense?
 
1. Create a new field in the underlying table of your combo box's record source. This field would be for the account number.
2. Amend your INSERT statement to include the new field.
3. If you want your combo box to show the account number also then include change the Column Count and Column Width properties of the combo box.
 
I don't know why this has to be so difficult. If you read my original post, it explains i already have a table with both fields. Please please please consider what i'm asking. I need to be able to add data into field 1 (account number) and field 2 (account name). My combo box ONLY needs to have field 1 in it. but when a new value is added to field 1 by a "not in list" event procedue, i need some way to also enter another value (account name) into field 2. There has GOT to be away to do this!
 
Just create a form (bound to the table you need the values in) to add the value and the text for the 2nd field. And then modify your code to change to use the form instead of the SQL insert.

Code: 
[FONT=Times New Roman]Private Sub Combo187_NotInList(NewData [COLOR=navy]As String[/COLOR], Response [COLOR=navy]As Integer[/COLOR])[/FONT]
[FONT=Times New Roman]   [COLOR=navy]On Error GoTo [/COLOR]Combo187_NotInList_Err[/FONT]
[FONT=Times New Roman]   [COLOR=navy]Dim [/COLOR]intAnswer [COLOR=navy]As Integer[/COLOR][/FONT]
[FONT=Times New Roman]   [COLOR=navy]Dim [/COLOR]strSQL [COLOR=navy]As String[/COLOR][/FONT]
[FONT=Times New Roman]   intAnswer = MsgBox("The Account Number " & Chr(34) & NewData & _[/FONT]
[FONT=Times New Roman]       Chr(34) & " is not currently listed." & vbCrLf & _[/FONT]
[FONT=Times New Roman]       "Would you like to add it to the list now?" _[/FONT]
[FONT=Times New Roman]       , vbQuestion + vbYesNo, "Account Information")[/FONT]
[FONT=Times New Roman]   [COLOR=navy]If [/COLOR]intAnswer = vbYes [COLOR=navy]Then[/COLOR][/FONT]
[FONT=Times New Roman]       [B][COLOR=red][SIZE=3]DoCmd.OpenForm "YourFormNameHere", acNormal, WindowMode:=acDialog[/SIZE]        [/COLOR][/B][/FONT]
 
[FONT=Times New Roman]       MsgBox "The new account number has been added." _[/FONT]
[FONT=Times New Roman]           , vbInformation, "Account Information"[/FONT]
[FONT=Times New Roman]       Response = acDataErrAdded[/FONT]
[FONT=Times New Roman]   [COLOR=navy]Else[/COLOR][/FONT]
[FONT=Times New Roman]       MsgBox "Please choose an account number from the list." _[/FONT]
[FONT=Times New Roman]           , vbInformation, "Account Information"[/FONT]
[FONT=Times New Roman]       Response = acDataErrContinue[/FONT]
[FONT=Times New Roman]   [COLOR=navy]End If[/COLOR][/FONT]
[FONT=Times New Roman]Combo187_NotInList_Exit:[/FONT]
[FONT=Times New Roman]   [COLOR=navy]Exit Sub[/COLOR][/FONT]
[FONT=Times New Roman]Combo187_NotInList_Err:[/FONT]
[FONT=Times New Roman]   MsgBox Err.Description, vbCritical, "Error"[/FONT]
[FONT=Times New Roman]   [COLOR=navy]Resume [/COLOR]Combo187_NotInList_Exit[/FONT]
[FONT=Times New Roman][COLOR=navy]End Sub[/COLOR][/FONT]
 
You must log in or register to reply here.

Similar threads

W
Table fields
Replies
7
Views
558
Cotswold
Cotswold
W
Solved Table Updates - PK changes - help
2
Replies
25
Views
1,915
wmix
W
M
I'm sure there is a simple solution asset tracking MY NEW PROJECT.
2
Replies
29
Views
753
Ken Sheridan
Ken Sheridan
tmd63
How to use a combo box to filter a forms data
Replies
2
Views
657
theDBguy
theDBguy
B
Table - After Update question
Replies
9
Views
797
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom