Results 1 to 12 of 12
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    May 2013
    Posts
    114

    Not in List

    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



  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,895
    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.

  3. #3
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    May 2013
    Posts
    114
    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?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    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

  5. #5
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    Thank you Micron and ItsMe. The information was extremely helpful.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,895
    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.

  7. #7
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    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

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    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

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    Yes, that is what is happening.

    The acDialog mode is crucial for this to work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    114
    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

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,215
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-22-2015, 12:30 PM
  2. Replies: 1
    Last Post: 04-21-2014, 08:00 AM
  3. Replies: 15
    Last Post: 11-20-2013, 04:30 PM
  4. Replies: 2
    Last Post: 04-05-2012, 08:39 PM
  5. Replies: 4
    Last Post: 06-16-2011, 09:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums