Page 1 of 4 123 ... LastLast
Results 1 to 15 of 60
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Can't figure these two things out

    I'm having difficulties figuring out why the frmIndividual is prompting for a "Select Parameter" when you try to load it. If you cancel through and then select the "Visa Information" on the first tab control, last tab, it has a subform on it. In the listbox of "Visa Type", you are supposed to be able to double-click the listbox, it's supposed to open the frmFindVisa where the user can search for the type of Visa. Then when they double-click the correct one, it should pass the value back to that listbox.

    I CAN do this if you open the subfrmIndVisa and it works fine, I just can't figure out WHY it won't work from the main form (frmIndividual) AND why it's prompting for a "Select Parameter" when you first open the frmIndividual.

    Any ideas on what I did wrong?
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm working with Access 2007 so I'm having a bit of a time learning it and navigating around but if it helps any, if you remove the rowsource for the List9 listbox on the subfrmIndVisa (or remove the criteria from the query), frmIndividual works.

    Also, on frmIndividual, you may want to consider setting the sourceobject of the subform's once that tab is clicked (just something to speed up your form.)

    I'll try to look at this on my Access XP machine.
    Last edited by pkstormy; 08-20-08 at 20:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    I'm working with Access 2007 so I'm having a bit of a time learning it and navigating around but if it helps any, if you remove the rowsource for the List9 listbox on the subfrmIndVisa (or remove the criteria from the query), frmIndividual works.
    I tried that. It worked but then the subform subfrmIndVisa doesn't work the way it's supposed to where the user double-clicks the listbox, the form frmFindVisa opens, then the user searches for the Visa Type and it passes the information back into the listbox in the subfrmIndVisa

    Also, on frmIndividual, you may want to consider setting the sourceobject of the subform's once that tab is clicked (just something to speed up your form.).

    I don't follow this Paul. All of the subforms are linked by IndID (the pk in the main table) so I figure that they'd all link back and be able to save the information based off of that.

    The second set of tab-controls, I'm planning on making it so that when the user clicks on the 1st tab, 1st tab-control, it opens the same on the lower one BUT has ALL of the instances on that SAME individual displayed in a listbox so the user can then see the information.

    Could you suggest an easier / better way if the way I'm doing it or thinking about it is wrong?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the problem with the Visa subform....

    You were missing Forms!frmIndividual!subfrmIndVisa (you just had Forms!subfrmIndVisa) in the listbox sourceobject and rowsource query criteria.

    Regarding setting the sourceobject on the subforms...

    See the attached example. I added a few lines of code and set the sourceobject of the subform's to blank. See the comments in the code. I like doing it this way as the user cannot then add information to the subform's without adding information into the frmIndividual (top part) first. You'll notice that I use IndID to test if it is null. (Note: you may want to add some code to handle the Gender and Ethnicity fields being blank as adding a new record and not populating these fields throws up an error - I would recommend putting in some code to handle these fields being null versus the error MSAccess generates.)

    Keep in mind that I modified the frmIndividual fairly quickly but you'll see that I added code in a couple of places and removed the sourceobject of the subforms (except the first tab's subform as I set the frmIndividual DataEntry to false and added a button to create a new record.) I sometimes have a "MainForm" where I have a button to add a new record so this would be different in that scenario. If you make it so the DataEntry = True on frmIndividual, you'll probably want to handle any cases where the user might enter information in the subform's first before entering information in the top part of frmIndividual (although I don't know why they would but you never know with some users.) So if you want the DataEntry to be true on frmIndividual, then you could set the sourceobject of the subform on the first tab (such as I did in the Select Case statement) after the user has entered some information (such as FirstNM) which would then generates an IndID and then doesn't give you any errors entering information in the subform. The key is....first make sure an IndID is generated, and then set the sourceobject of the subform on whatever tab the user might be on or make sure that the tab control is set to the first tab (ie...tab # 0). Hope that makes sense. I could have done it this way but I wanted to show you what I recommend.

    You can use this code as an example for your other tab control and any subforms with it. Again though, this is my personal preference on how I would do it so you don't need to use this code if you don't want to. If you have a lot of records, loading all the subforms when the frmIndividual opens will take a fair amount of time if the sourceobjects of the subform's are not blank (unless DataEntry is set to True - then you have to again, make sure IndID is generated first for the subform entries - if not, you tend to get problems with the subform's if the user starts clicking the tabs and tries to enter stuff into the subforms first or if you have controls/queries in the subforms based off a record on the IndID in the frmIndividual.) Otherwise, as I have it, the form will always load quickly regardless if you have a large recordset and also allows the user to navigate between records (I also set the Show Navigation buttons property to Yes) and tests to see if IndID is not null to populate the subform of that tab (see the OnCurrent event of frmIndividual and the OnChange event of the tab control TabCtl39.) These were the places I put in the code.

    Hope that helps and sorry about rambling on. Again, I did this fairly quickly and without a lot of actual data, I couldn't test all possible scenarios.

    As one last note - putting . (period) in the mdb name is not advisable. It "could" give you some problems in the future with accessing the mdb over the network or linking the tables should you split the mdb.
    Attached Files Attached Files
    Last edited by pkstormy; 08-21-08 at 05:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I thank you for helping me out. I'm looking at it now. I printed out some of the code, specifically for frmIndividual. In the On_Current event on that form, you have code that says it sets all the subform sourceobjects to blank if it is a new record.

    My question is, Will this affect the way that they are related? I mean like since when a new Individual is entered, all of the subforms should have the same IndividualID that the main form, frmIndividual, has on it so that the information entered all goes back to that single individual?

    I haven't gone through it all yet, but I'll probably have more questions or need for explainations so I hope you don't get flustered with me.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Regarding the problem with the Visa subform....

    You'll notice that I use IndID to test if it is null. (Note: you may want to add some code to handle the Gender and Ethnicity fields being blank as adding a new record and not populating these fields throws up an error - I would recommend putting in some code to handle these fields being null versus the error MSAccess generates.)
    I noticed that it gives that error message also. But I looked at the field properties in the table and it is NOT a required field so I don't know why it is generating that error message for the Gender and Ethnicity fields. That was something that I was looking at stopping or trying to stop because it is very annoying.

    I'm going to look at yours, since I saw that it doesn't generate that error, and see what you have that's different from the one that I have.

    Thanks for all your help!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Grafixx01
    Paul,

    My question is, Will this affect the way that they are related? I mean like since when a new Individual is entered, all of the subforms should have the same IndividualID that the main form, frmIndividual, has on it so that the information entered all goes back to that single individual?
    No (at least it hasn't ever affected related things when I've done it - but there is 1 thing I need to check on your subforms.). When the subform's sourceobject is populated, the link main/child relationship should still be actively related.

    I want to look into why you're getting the error with the tblGender and tblEthnicity. That bugs me. Get right back to ya.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok...I've looked at your subform's a little more closely and there's something I realized you aren't doing on your subforms. What I usually do (and recommend) is that your IndID field on the subforms is first, a textbox, not a combobox. And also, the default value is set to =Forms!frmIndividual!IndID.

    I've got a little bit of time here so let me come up with a new sample for you. I want to test fix the tblGender and tblEthnicity error also.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok...Here's a new version and what I fixed:

    1. You want to make your recordsource query for each of the subform's based on criteria which =Forms!frmIndividual!IndID for IndID. Look at the recordsource for each of the subforms in this new example. This ensures each subform returns only the record matching the IndID on the frmIndividual.

    2. I usually like to show (and disable), the IndID field. I've also changed these to textboxes and set the default value =Forms!frmIndividual!IndID. This ensures that each subform is populated with the value from the frmIndividual with the correct IndID.

    This is the way I've always done this for many, many years and it has never given me any problems plus it works very efficiently and fast for record retrieval (and user's usually like to navigate between records). If you don't want to utilize the setting the sourceobject method, please keep 1 and 2 above for your subforms. It will save you a lot of pain and troubleshooting in the long run!

    Also as a recommendation, when creating a lot of subforms like this project, what I'll do for naming them is something like this:
    frmIndividual (main form)
    frmIndividualSubPassport (subform for Passport)
    frmIndividualSubTravel1 (subform for Travel1)
    frmIndividualSubTravel2 (subform for Travel2)
    frmIndividualSubVisa (subform for Visa)

    What this does is make it nice and easy in the form listing to quickly find the subform's related to frmIndividual. Just something I do and the way you've named them is pretty easy to find but I just wanted to throw this out there.

    Now I'll figure out what's going on with tblGender and tblEthnicity.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Ok...Here's a new version and what I fixed:

    1. You want to make your recordsource query for each of the subform's based on criteria which =Forms!frmIndividual!IndID for IndID. Look at the recordsource for each of the subforms in this new example. This ensures each subform returns only the record matching the IndID on the frmIndividual.

    2. I usually like to show (and disable), the IndID field. I've also changed these to textboxes and set the default value =Forms!frmIndividual!IndID. This ensures that each subform is populated with the value from the frmIndividual with the correct IndID.

    This is the way I've always done this for many, many years and it has never given me any problems plus it works very efficiently and fast for record retrieval (and user's usually like to navigate between records). If you don't want to utilize the setting the sourceobject method, please keep 1 and 2 above for your subforms. It will save you a lot of pain and troubleshooting in the long run!

    Also as a recommendation, when creating a lot of subforms like this project, what I'll do for naming them is something like this:
    frmIndividual (main form)
    frmIndividualSubPassport (subform for Passport)
    frmIndividualSubTravel1 (subform for Travel1)
    frmIndividualSubTravel2 (subform for Travel2)
    frmIndividualSubVisa (subform for Visa)

    What this does is make it nice and easy in the form listing to quickly find the subform's related to frmIndividual. Just something I do and the way you've named them is pretty easy to find but I just wanted to throw this out there.

    Now I'll figure out what's going on with tblGender and tblEthnicity.
    Paul,

    I'm going over it now. I've changed the IndividualID to text boxes in the tables and then readded them to the forms. They stay the same with the control source in there properly. Thanks!

    The question I have, it may be the planning on my part, is if an Individual's State ID / Passport / Visa expires, the user will be able to enter in a NEW one. This will be done in the first set of tab-controls in the subforms. Is this going to cause any issues with anything we've changed?

    What I'm trying to do is when the user selects the tab on the first tab-control, the same tab on the second (bottom) tab-control displays the INFORMATION in a listbox on a subform on that particular Individual so the user can compare / view it and maybe raise questions.

    So... If the user has a passport and it expires, with the OLD passport information and NEW passport information, it would show 2 passports for the same individual in the bottom tab-control along with the information from the passport (like countries they've traveled to on the new and old one).

    You see any issues or reprogramming that I may have to do with the top tab-control and subforms to accomplish this?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok...here's a new version. There are some concerning things I need to point out.

    1. NEVER EVER (EVER) use the Lookup tab for any field in any table (ie. in design view of a table). This is the WORST feature Microsoft put into MSAccess. You had this in for several ID fields and as you could see, it causes problems (once I removed this and set the default value for GenderID and EthnicityID to blank, it worked fine.) I removed all of the Combobox (lookup tab) settings in all the tables for all the fields which had this. Microsoft put this "feature" in for directly entering data into a table (which is never recommended.) It will only cause you sever headaches (it's "only" benefit is seeing the data in the relational table without having to create a query and link the 2 tables but trust me on this - it isn't worth it!) I hope you'll see additional posts from others supporting not doing this.

    2. I didn't realize this but you have multiple subforms under the tabs which are based on the same table. This won't work as when a user enters info into say, US Travel 1, the form US Travel 2 will show the same record. This is the same situation for the other subforms which are all based on the same table.
    Look at the way I've setup the US Travel 1 and 2 subforms. I made it so US Travel 2 is an unbound (ie. no recordsource) subform with a listbox showing the values in the table. When a user clicks on the listbox, it then sets the focus to USTravel1 subform and goes to the record selected in the listbox. In the USTravel1 subform, I made it so in the AfterUpdate event (ie. you go to a new record), it requeries the listbox in the USTravel2 subform. I also added a button in USTravel1 subform to go to a new record and enabled the Navigation buttons so the user can see how many records at the bottom of this subform (as well as see how many records are in the listbox showing in USTravel2 subform.)

    You'll need to fix the other multiple subforms based on the same table in the other tabs. You can make the 2nd form a listbox type form and you can remove the 3rd subform or make it also another listbox type subform (so you have 2 listbox subforms) like I did with USTravel2 subform (Remember though if you remove any forms, you'll need to change the Select Case in frmIndividual and remove the code which populates the sourceobject with the subform name!) But you do need to do something with the other multiple subforms based on the same table (I personally like having a listbox type subform which shows all the records but it's your choice on how you want to do this. You could remove the 2nd and 3rd subforms and stick with just the 1 subform and the user utilizes the navigation bars.) Don't try and make it work though with work-around code to try and make it so you can have multiple subforms based on the same table - this is a bad idea.

    I also set the navigation to true for the visa subform (as I did with some of the other subforms which were relational with multiple records) and set Data Entry = False for all subforms. If you only want to have 1 record for a subform, just set the navigation to false. But having DataEntry = true for these subforms makes it very confusing for the user as they think their information dissappeared when they click back onto that tab. I would NOT recommend setting any subforms Data Entry = true but instead having an "Add" button to add a new record or letting the user use the navigation bar to go to a new record (as well as navigate between records.) If the user enters a new record in the frmIndividual, when they click on the tab below, Access is smart enough to know that it's automatically adding a new record in the subform so having Data Entry set to true just makes it confusing and is unnecessary.

    Minor change - I made it so after the first name is entered (ie. AfterUpdate event which has then generated a new IndID) on a new record, the first tab shows it's subform (so the user doesn't have to actually click on the tab to show the subform.)

    I tested out entering new records and navigating between records and everything worked fine. Keep in mind though that I just started looking at this the other day and maybe spent 5-6 hours on changes. Since I've done this kind of stuff over and over again, I used the techniques I've always used which have worked very well for me but there might be a couple of minor things I missed (others might do it differently but if you do anything, please just heed my warnings above.)
    Attached Files Attached Files
    Last edited by pkstormy; 08-21-08 at 17:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I like what you did. I'm going to fix the other one similar to that. I was thinking about putting a listbox on the same tab like you did so it'll show what is already in that individual's record without having the tab-control at the bottom and then having to figure out the code to say like "If tab-control 1, tab 1 then tab-control 2, tab 1..." I figure it'd be too much to work out.

    Thanks for the help. I'll post what I get after I look at the changes into the one I'm doing now.

    Thanks again!

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    When I display more in the listbox on say "subfrmInUSTravel2", such as the Vehicle Maker and other fields that are lookups off of another table, how would I be able to display the ACTUAL name of the field rather than the ID number?

    I can't figure this part out. The two ways I'm thinking of I know are either wrong or can't do.

    1. Put in the actual field name of desired into the SELECT query.

    2. Put a hidden field on the form, pass the value from the SELECT query source of the listbox to that txtbox, then the actual word value back to the listbox.

    I have no clue.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Grafixx01
    When I display more in the listbox on say "subfrmInUSTravel2", such as the Vehicle Maker and other fields that are lookups off of another table, how would I be able to display the ACTUAL name of the field rather than the ID number?
    I "think" you're referring to going into the design view of the query for the listbox rowsource. You'd then want to add and link the relational tables in the query and then drag whatever column from the relational table down into the query.

    Quote Originally Posted by Grafixx01
    1. Put in the actual field name of desired into the SELECT query.
    Not sure what you mean here. Can you clarify?

    Quote Originally Posted by Grafixx01
    2. Put a hidden field on the form, pass the value from the SELECT query source of the listbox to that txtbox, then the actual word value back to the listbox.
    Remember that in referring to listbox or combobox columns, the 2nd column (in the rowsource query) is really referred to in code as column 1 - ie. =me.MyListBoxField.Column(1) - and so on so the 3rd column =me.MyListBoxField.Column(2), etc...whereas the 1st column is referred to in code as just: =me!MyListBoxField.

    So to make it so a column value from a listbox is passed to a textbox field, you'd put in (for the sourceobject of the textbox field) =me.MyListBoxField.Column(1) or whatever column matches the query column you want to return (or you'd leave the sourceobject of the textbox blank or bound to the actual field (versus the equation) and put in code in the AfterUpdate event of the listbox such as: me!MyTextField = me.MyListBoxField.Column(1)

    I'm not quite sure why or what you want to do by passing the actual word value back into the listbox (versus just make the textbox field unhidden but locked =true and enabled = false.) Can you please clarify what you're trying to accomplish. I have a gut feeling that what you're trying to do can be done in a different way that the way you're trying to do it.

    Are you trying to update some value in a column from the listbox? - You may just need to requery the listbox after you populate the value into the textbox field.

    Or are you trying to update a value in the relational table which the rowsource query for the listbox might be linked to?
    Last edited by pkstormy; 08-21-08 at 20:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    I "think" you're referring to going into the design view of the query for the listbox rowsource. You'd then want to add and link the relational tables in the query and then drag whatever column from the relational table down into the query.



    Not sure what you mean here. Can you clarify?



    Remember that in referring to listbox or combobox columns, the 2nd column (in the rowsource query) is really referred to in code as column 1 - ie. =me.MyListBoxField.Column(1) - and so on so the 3rd column =me.MyListBoxField.Column(2), etc...whereas the 1st column is referred to in code as just: =me!MyListBoxField.

    So to make it so a column value from a listbox is passed to a textbox field, you'd put in (for the sourceobject of the textbox field) =me.MyListBoxField.Column(1) or whatever column matches the query column you want to return (or you'd leave the sourceobject of the textbox blank or bound to the actual field (versus the equation) and put in code in the AfterUpdate event of the listbox such as: me!MyTextField = me.MyListBoxField.Column(1)

    I'm not quite sure why or what you want to do by passing the actual word value back into the listbox (versus just make the textbox field unhidden but locked =true and enabled = false.) Can you please clarify what you're trying to accomplish. I have a gut feeling that what you're trying to do can be done in a different way that the way you're trying to do it.

    Are you trying to update some value in a column from the listbox? - You may just need to requery the listbox after you populate the value into the textbox field.

    Or are you trying to update a value in the relational table which the rowsource query for the listbox might be linked to?
    I knew that I'd confuse whoever was going to read it and try to figure out what I was talking about.

    If you look at the last edited db that you posted, and on the "Inside US" subform where it displays the information in a listbox, there are other fields, as you can see from the first subform to the left of it, that I was trying to put into that listbox. Like the "Vehicle Manufacturer", rather than it reading like "2" for Audi, it would actually say "Audi".

    Is that less confusing?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •