Results 1 to 10 of 10
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Address By Location Qry?

    MyProfileTest Copy.zip

    HI all,
    I am working on this db and cannot figure out how to create the correct query for my address form as it needs to be not only by company but by locations. I have set up the query yet it fills in the
    location by random and i need to specify it. On the before update event of the combo addresstype, i open a form called select locations, which is just locations for that company, should put the ID for that
    location in the filed on the subform (sfrmAddress). Not sure how to explain it.

    In plain english, I open up company form, select tab /Button called address which opens my sfrmAddress, then i select from the combo CboAddressType, that will open the pop up form to select a Location realted to the open company, and that value or ID needs to go into the subform (TxtLocationID)



    This is a big one.... Any suggestions....
    db Attached, opes to company form

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In your form Selection, maybe for your popup form button
    Code:
    Private Sub Command2_Click()
    Forms!frmCompany.sfrmCompany.Form.TxtLocationID = Me.CboLocation
    DoCmd.Close
    End Sub
    I see that your code won't fire if there was a value there and it was changed. Currently that means that the id value won't change but the combo value will. Not only that, you can't cancel the popup if you made a mistake on the subform combo selection. I'm speculating that one might realize that based on the selection you expect certain list values. When you don't see what you expect you look at the form and think "shoot, I picked the wrong value. Now what? I can't cancel." Maybe that's not the case, but it's generally not a good idea to present an input form with no way to cancel.
    Last edited by Micron; 03-06-2021 at 01:49 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks Micron,
    I will give this a shot, and yes, i was going to put a cancel button in the form,,,, its a work in progress

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi, agree with Micron that you might want to rethink your design. Made a few changes, seems like it does what you want but not sure. Also changed the Switch_Tabs sub to allow for setting child\master linking for the subforms.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Click image for larger version. 

Name:	Error.jpg 
Views:	11 
Size:	105.0 KB 
ID:	44509

    HI Micron,
    OK, that worked really good but i forgot i didnt have CompanyID as so it would only be to that company in the query. So, I added that to the query from the existing location table in query and when i veririfed it worked i got this message.
    ??????????????????????????

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Gicu
    That worked as i imagined it.... Thank you so much as i have been working on that all day long... Wow..
    Thanks
    Dave

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave,
    As you noticed your original subform was missing the CompanyID so moving from record to recond in the main from was not refreshing the subform. That is why I modified the Switch_Tabs code to allow for optional child\master (in case they are different than the ones saved with the subform). Also added the CompanyID as a dlookup (as if you add the tblCompanyLocations to the query you will actually create new empty records in the later table).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    HI Gicu
    Nice work! I like what you did now that i have had time to review. I do have a question as I will be using the same senario for the phone tab and sub form, and also the contact form when i start setting that up so my contacts can be by location so the question is, would you use a new Select Location form for each of them or do somehting on open args, or on load, ;;;;?????

    Thanks
    Dave

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave,
    I would stick with one form for sure and use OpenArgs to pass the control name that needs to be updated (as the subform control name stays the same). If you need to pass multiple arguments I use a pipe (|) delimited string and parse that out on the Open event using Split().
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you
    I will work on that one....
    Dave

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

Similar Threads

  1. Replies: 11
    Last Post: 02-25-2019, 02:09 PM
  2. Replies: 4
    Last Post: 01-06-2018, 03:26 PM
  3. Replies: 7
    Last Post: 06-27-2016, 12:28 PM
  4. Replies: 2
    Last Post: 08-22-2013, 12:02 AM
  5. Input Mask for an IP Address and Mack Address
    By baksg1995 in forum Access
    Replies: 18
    Last Post: 06-23-2009, 12:33 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