Not in List

[lefty2cox]

Hi

I got this code set up for a not in list event. It works pretty well but I can't figure out how to make the new value available after entry. I've tried to requery, recalc and refresh on the on focus, on current and on activate commands but can't seem to get my groove on. The form I'm working on is named "LedgerForm" and it remains open during the opening of "frmPayee" right now. Here's my code. I'm really new at VBA so I apologize.

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


Response = acDataErrContinue
Dim Answer As Integer


Answer = MsgBox("Do you want to add " & NewData & " as a new payee?", vbYesNo, "Add new payee?")


If Answer = vbYes Then
DoCmd.OpenForm ("frmpayee"), , , , acFormAdd
Forms.frmPayee.payee = NewData

Else
Me.PayeeID.Value = Null

End If



End Sub


Thanks


Lefty

[Micron]

You have to modify the rowsource using the value that the event returns from the prompt as shown here - https://msdn.microsoft.com/en-us/library/office/ff845736.aspx
You can't assign it to the control itself, especially if it's bound.

[lefty2cox]

Ah...I see. So if I understand correctly, I need to be entering NewData directly into the table with my code as opposed to trying to set the value of a control on a form that is bound to the table?

[ItsMe]

If the Rowsource of the combo is based on a table or a query ...

From frmpayee you can use code to commit the value in payee to the table and then requery the combo in the first form. Forms!FirstFormName!.ComboName.Requery

Also, it is advisable to commit any changes in the first form to the table in case there is a shared field with frmpayee's recordsource.

If Answer = vbYes Then

if me.dirty = true then
me.dirty = false
end if

DoCmd.OpenForm ("frmpayee"), , , , acFormAdd

[lefty2cox]

Thank you Micron and ItsMe. The information was extremely helpful.

[Micron]

It was my understanding you are not trying to set the value of a control or make an entry into a table, but that you want to modify the value list for a listbox or combo box. Am I wrong about that? As ItsMe indicates, what you need to do depends on where your data is: table or value list. Perhaps you should elaborate on the process if the last suggestion doesn't work.

[lefty2cox]

Sorry about the confusion. The drop down is bound to a table called payee. The drop down exists on the "ledgerform". The row source for the drop down is an embedded select query (i think this is the proper terminology) that returns two columns from the table "payee", primary key and PayeeName. So in the properties for the drop down I have 2 columns, column 1, the bound column, is the primary key. I have the column widths set to 0",1" so the user sees only the payee name.

My goal is modify the "payee" table by adding the not in list text as a new payee name to the payee table, and then having the "payee" record immediately available for selection in the drop down box on the ledger form.

Initially, I was thinking I would have that event trigger the opening of the form I had created for the payee table and then after the insertion of the the new payee record, perform some sort of automated refresh or requery on the payee drop box, on the first form, ledgerForm.

But after your suggested I checked out that website, I found that the code there resulted in a much better way for this particular procedure. It actually entered the info in the back round directly into the table. I copied and pasted the code almost verbatim.

The "payee" table has only a few fields. ID, Name, Address,Town,zip and email. The only required fields are ID and payee name. So I went with the stock code from that site with 3 modifications. I commented out the For Next loop (just commented it out but left it for possible later use) and I commented out the last two commands which opened the table and navigated to the new record. I also added an else to the if block if the user clicks "no" in the original msgbox. So now when the user clicks "yes" to the msgbox asking whether to add the not in list text as a new payee, it just gets added to the payee table and magically appears in the dropdown. See below for the code I ended up with. How did I do? it seems to work well but you know how that goes.


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


Dim oRS As DAO.Recordset, i As Integer, sMsg As String
Dim oRSClone As DAO.Recordset


Response = acDataErrContinue


If MsgBox("Add Payee?", vbYesNo) = vbYes Then
Set oRS = CurrentDb.OpenRecordset("payee", dbOpenDynaset)
oRS.AddNew
oRS.Fields(1) = NewData
' For i = 2 To oRS.Fields.Count - 1
' sMsg = "What do you want for " & oRS(i).Name
' oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
' Next i
oRS.Update
PayeeID = Null
PayeeID.Requery
' DoCmd.OpenTable "payee", acViewNormal, acReadOnly
' DoCmd.GoToRecord acDataTable, "payee", acLast


Else
MsgBox ("Please select a payee from the list.")
End If




End Sub

[June7]

Proper coding can:

1. open form to add record to lookup table

2. requery the combobox so new item is listed and available

3. set the combobox value to the new item

Review https://www.accessforums.net/access/...ate-55139.html

[lefty2cox]

Great thread June7. Thank you. I grabbed the code you suggested in the link and made a couple mods, as you had suggested in subsequent posts on the thread.

I was struggling to figure out why it worked though, specifically how you got the new location to be available immediately in the drop down. Tell me if I'm on track...

Below is the code from your thread. I also took the "if me.newrecord then me.location = me.openargs" section and added that to the frmLocation...of course replacing the names with the names I'm using. Everything appears to work great

It appears that me.location.requery is the key to making the new location available immediately. When I was performing this before my original post, I was always opening the new form in normal view. It appears you are opening the form as a dialog, which stops the code until the dialog box is closed???? Hence running the requery at just the right time, after the new record has been saved in the location table? Where as when I was running a similar code, the new item was NOT available without manual refresh because the normal view did not stop the procedure and my requery code occurred before the new record was added to the location table??

I hope I'm on track....I just pulled a hamstring thinking about this.

Public Sub Location_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
Me.Location = Null
If MsgBox("Do you want to add this Location?", vbYesNo, "Add New Location?") = vbYes Then
DoCmd.OpenForm "frmLocation", , , , acFormAdd, acDialog, NewData
Me.Location.Requery
Me.Location = NewData
Else
DoCmd.GoToControl "Location"
End If
End Sub

[June7]

Yes, that is what is happening.

The acDialog mode is crucial for this to work.

[lefty2cox]

You have no idea how happy it makes me to hear you say that.

If I could ask, I know there is an abundance of guidance on the interwebs, particularly the MS site you sent me, on VBA for access. I was wondering if there were any books I could pick up on just VBA coding for access. I find a book to be an invaluable learning tool for me because of my schedule.

I've read Access 2013 The Lost Manual but it seems to focus mainly on tables, queries, forms and reports, which I feel I've got down. There's a little VBA in the book but I wanted to take VBA to the next level.

Thanks for the help
Lefty

[June7]

Review https://www.accessforums.net/access/...ave-55379.html