Page 4 of 4 FirstFirst 1234
Results 46 to 57 of 57
  1. #46
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your zip file in #44 only has the lock file. This is not the database.

  2. #47
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Dogs032217.zip

    Not sure how/why that happened.

    Things are progressing fairly well. the "people" worked out well so far and i've moved on to dogs. dogs are more complicated, but it's going well.

    I'm wondering if you can easily answer something i've been working on. When entering a new dog the user will enter the dog's name and other info. they will likely want to enter a "sire". if the sire is not in the list i want to take them to a "add sire form". that's straight forward. When they've completed entering their new Sire and close the form i want the user returned to the main dog form and for that new sire to appear in the sire cbo on the main dog form the user was working on.

    Also, along the same lines, if the user finds the Sire but the sire requires correction i've include an "Edit Sire" button that will take the user out to an "Edit Dog Form" and the Edit Dog Form will open to that Sire's record - i've got that part. But after the user has completed their corrections to the Sire, how do i return the user to the main dog form, where the were previously, with the now edited Sire appearing in the Sire combobox?

  3. #48
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just saw your post. I'm going to give my first impressions/thoughts on how I might approach
    When entering a new dog the user will enter the dog's name and other info. they will likely want to enter a "sire". if the sire is not in the list i want to take them to a "add sire form". that's straight forward. When they've completed entering their new Sire and close the form i want the user returned to the main dog form and for that new sire to appear in the sire cbo on the main dog form the user was working on.
    This is a first guess(totally untested)
    If you are working on a data entry form (A) and have to add or modify a record that is needed to do the work on (A), you might open another form (B) in modal/dialog mode. Complete the activity on (B) and close(B). When you go back to (A), I think you'll be where you were before you went to (B). But, because a control on (A) needs that record from (B), you'll have to do a requery of (A) to get the latest changes.

    You might want to look for an example or video for details.
    This is similar, but doesn't show some details
    The key to this is that the dialog form gets control -other stuff is put on hold -when you're done with the dialog form and close it, then you return to your position before the dialog was opened.
    Check youtube for more videos on opening a form in dialog mode.

    You could mock something up and try it. Get the mock up working, then using the technique as a guide, adjust your evolving data base. And since this could be a Sire or Dam that is missing, you'll have to handle either/both situations. And to repeat, you'll have to requery the control to give you that new DogID value for your combobox.
    Last edited by orange; 03-24-2017 at 06:22 AM. Reason: additional info

  4. #49
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have made a small mock up using 2 forms:
    -frmDogSireDamAssignment --which is the form to add dog info, sire, dam etc
    -AddMissingDogToTable-- which simulates needing a way to get Dam or Sire info that isn't in the Dog Table

    The scenario is you are working on record DogName Ellie-Mae, you have the Sire (JackSprat) but you don't have the Dam.
    Click image for larger version. 

Name:	ProcessingDogNeedToAddNewDam.jpg 
Views:	89 
Size:	40.9 KB 
ID:	27979
    You click the button to open the second form. This form is opened in Dialog mode.

    This the code that runs when the button is clicked. Notice the OpenForm parameter 'acDialog'
    Code:
                          
    Private Sub btnNewDog_Click()
    On Error GoTo Err_btnNewDog_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "AddMissingDogToTable"
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog
        
    Exit_btnNewDog_Click:
        Exit Sub
    
    Err_btnNewDog_Click:
        MsgBox err.Description
        Resume Exit_btnNewDog_Click
        
    End Sub


    Click image for larger version. 

Name:	OpenAddMissingDogDialog.jpg 
Views:	89 
Size:	56.8 KB 
ID:	27980

    You enter the new Dog (CinderBlock) then click the Close button.

    This is the code behind the Close button on the AddMissingDog form
    Code:
    Private Sub btnClose_Click()
    On Error GoTo Err_btnClose_Click
    
    
        If Me.Dirty Then Me.Dirty = False
        DoCmd.Close
        '
        'Get latest values from females in DogTest as rowsource
        Forms!frmDogSireDamAssignment.cboDam.Requery '<---this requeries the cboDam rowsource
    Exit_btnClose_Click:
        Exit Sub
    
    Err_btnClose_Click:
        MsgBox err.Description
        Resume Exit_btnClose_Click
        
    End Sub
    You are returned to the first form
    Click image for larger version. 

Name:	ReturnToTheOriginalDog Assignmentform.jpg 
Views:	89 
Size:	118.2 KB 
ID:	27981

    Notice that CinderBlock is a selectable option in the cboDam.
    And you are returned to your original position on the Assignment form.

    This is the code that gets "rerun" when the requery is done.
    It gets the latest values from the DogTest table as rowsource of cboDam.

    Code:
    cboDam.RowSource = "SELECT DogTest.DogId, DogTest.DogName " _
                          & " From DogTest " _
                          & " where dogtest.gender = 'f' and " _
                          & " dogid <> " & Me.cboDog _
                          & " ORDER BY DogTest.DogName;"
    I realize you're using macros and I'm showing vba since I don't use macros. The concept/logic is the fact being highlighted.

    Good luck.

    PS: The text box in the middle of the Assignment form was to show the SQL statements to update the Dog record in the Dog table. It's for illustration only. After assigning CinderBlock as Dam (to Ellie-Mae) the SQL is
    Code:
    Update Dogtest Set Sire =4 Where dogId = 15
    Update Dogtest Set Dam =20 Where dogId = 15

  5. #50
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Thank you. Actually, it turns out there's already a solution built in to Access. in the Data tab on design view there's a spot called "List Items Edit Form". you simply enter the form you'd like to use in case of a "not in list" occurance and the user is taken to that form. Access takes care of all the programming to add the new item to the table, requery the combobox and include the newly added item for you.

    There is another issue i've been working on for a couple of days now. i'm going to try one more approach before i say uncle and ask for some guidance. other tan this issue however, everything is still progressing nicely.

  6. #51
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Very nice. I updated my mock up with that technique for the Dam. I haven't done a lot with forms since Access 2003- you learn all the time.
    Good idea and not in list and ListItems Edit form is described by Allen Browne

    Sounds like you're on a roll --keep going.

    Note: If anyone stumbles upon this and needs a tutorial on combo on form Edit List Item (here's one)
    Last edited by orange; 11-24-2018 at 11:24 AM.

  7. #52
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    yes thanks. figured out today's problem, and once again i was over-thinking.

    I have a Club Member From. You select the Club, Select the "member", and select the dues for "Club & Year" (establishing the DuesID), a paid button and Status selection (Active, Inactive....). I had trouble bringing in the ClubMemberRole (CMRole). I have a CMRoleJTbl, and i tried various forms of linking the master to the subform, but just couldn't get the name of the new "member" to show up in the subform. I first figured out that using "ClubMemberID" was a mistake. I already have a PeopleID to get the Club Member's name on the master to create the ClubMemberID in the first place. When entering a new member, this becomes a dirty record and the subform cannot possibly call the ClubMemberID until there is actually a ClubMemberID to call. So i changed the tables to use the PeopleID. The reason i initially wanted to use "ClubMemberID" was to limit the choices in certain other comboboxes, but i realized i can filter that inside the query.

    But although i could now link my Master to the Child via the PeopleID, ClubID, and DuesID, i still couldn't get the combobox in the subform to populate the "FirstName&" "&MdlInit+". "&LastName" for me. I could get the text box with the PeopleID to instantly appear when switching from name to name in the Master form, but the combobox containing the queried Name would not update until AFTER i assigned a Role to the individual.

    I have a suspicion this has something to do with related fields, or key fields. Then it dawned on me that I DON'T NEED TO SEE THE NAME IN THE SUBFORM. That's just overkill. i already have the name appearing in the master. All i have to is assign a role. and voila, done! It's not formatted for appearance yet, but it's functional and i can move on to the next item.

    The fog is indeed starting to lift. LOL
    Click image for larger version. 

Name:	2017-03-28_19-15-08.jpg 
Views:	84 
Size:	51.4 KB 
ID:	28042

  8. #53
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff. Your now up to your neck in analysis and that's good. One of my earlier questions re People vs club Members vs People with Roles in Events was are these all in your People table, or could some people outside your table have Event Roles?

    As you saw/determined with Dogs, A member of the Dog table could be a Sire. It's still based on the dog table.
    Sometimes the same table can be used to source other concepts.
    eg People --Victim, Perpetrator, Investigator, Lawyer, Judge---depends on the circumstances and context.

    This concept may also be called Reference Tables, or a role/rolename in modelling.
    eg. ISOCountryCodes

    each of these could get their appropriate values from the ISOCountry table.
    SupplierCountry
    SellerCountry
    ShipperCountry
    Last edited by orange; 03-29-2017 at 08:19 AM.

  9. #54
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    GM Orange,

    Things continue to progress. i haven't had as much time to work on the project as before, but still making small strides.

    My mind is now considering the next steps in the process. I'm confident i'll complete the current project of designing a db that actually works like a db for the user. Now i need to start thinking about how to publish the document for others to use.

    I could simply give away the file and let all the clubs who want to use it use it. We would all be keeping separate dog databases essentially. Or i thought i might find a way to make this file publicly accessible. Admittedly this is well outside my IT realm and i've read pros and cons about trying to turn access into a web-accessible product. But then i've read others that describe steps MS took in Access to allow it to be a web development product..???

    This is all volunteer, and i'm not interested in making a living off of the work. I'd be happy to allow others to use the end product free of charge, or for a nominal fee. I was wondering if you had any general suggestions for me in this area. I'd even entertain paying someone to help me produce it if we were talking about "nominal" money that i thought i could recoup from the clubs.

  10. #55
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have no experience with Access Web Apps but I am aware they are being sun-setted by M$oft.
    Access is not the data base for web. Our corporate databases pre-web were ADABAS, Those after 1995 were Oracle based.
    There are others on the forum who may have suggestions, but I'm not aware of anything that can use what you have developed.
    I have a colleague (another forum) who likes AlphaAnywhere, but it's expensive and I would suggest cost prohibitive for what you're doing.

  11. #56
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Thanks Orange. Great link and heads up to the winding down of Access on the Web.

    Power Apps looks to be the replacement solution going forward, and looks as though it's been developed to compete with systems like QuickBase. It's interesting. i'll be taking a closer look at that for a solution and i suspect i'll be able to copy and paste much of the existing work into this new app. i'm sure it will look and feel different, but that's ok.

  12. #57
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2015, 07:25 AM
  2. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  4. Replies: 6
    Last Post: 11-30-2013, 02:41 PM
  5. Replies: 2
    Last Post: 08-01-2011, 11:35 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