Page 4 of 4 FirstFirst 1234
Results 46 to 55 of 55
  1. #46
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Micron View Post
    I doubt that I'd ever use a nav form and I don't think I'm alone here with that sentiment, but I'm not saying I'd have 100% agreement. You'd need to understand their limitations, perhaps the biggest of which is that you can only open one form at a time. People have not realized that and started threads about why they can't get or pass values from one form that they opened when they switch. It's because the other one is now closed. Getting at the values in the forms is convoluted as well but it's doable.

    I see nothing wrong with using bound combos on forms for data entry only, or unbound for searching. If only some controls would be disabled, common approach is to
    - use control Tag property by giving it some sort of meaningful value (e.g. disable)
    - when form is opened from switchboard, the mode is passed to OpenArgs property of the form (e.g. read only)
    - loop over controls and if Tag property = disable, Enabled property is set to No.
    If all would be locked, use of OpenArgs sets form recordset to snapshot, thus can't be edited. Or loop as before without worrying about Tags but do worry about control type. You don't want to disable everything.

    When I create a NAV form I will defiantly be adding controls to disable the combo boxes based on whether or not you are viewing an established record or adding a new record. But at the current moment I am still having issues with duplicate values being added to my tables when I don't want or need them to be added.

    For example of if you open the table tblEquipCat you can see that I only have 4 entries on the table.

    Click image for larger version. 

Name:	tblEquipCat_BeforeNewRecord.png 
Views:	14 
Size:	10.5 KB 
ID:	49412

    Then if you open the form frmEquipTrack and change the category from the Category Combo box and select VTR. After you have selected VTR you can see the number 11 populate in the ID text box.

    Click image for larger version. 

Name:	Adding New Equipment Record .png 
Views:	14 
Size:	23.3 KB 
ID:	49413

    After that if you open the table tblEquipCat again you see a new entry on the table with 11 in the ID field and VTR in the EquipCat field

    Click image for larger version. 

Name:	tblEquipCat_AfterNewRecord.png 
Views:	14 
Size:	11.5 KB 
ID:	49414


    I haven't had this problem when I have created Combo Boxes via the wizard. I have been trying to look at the properties on the combo boxes that I have made using the wizard and I cannot find any differenced between the properties of the combo boxes that I converted from a text box and the combo boxes that I created via the wizard. Is there something that I might be missing in the setup of my query? If I keep getting an new entry in my tblEquipCat table every time I add a piece of equipment, the table will be unnecessarily large and utter useless.

  2. #47
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    I am a little puzzled with the construction of your frmEquipTrack.

    The Form is based on a query "EquipTrack_Qry" which has the "tblEquip" plus all 5 of the Lookup Tables needed to retrieve specific data.

    You do not need these 5 tables in the query that this form is based on.

    On the Form you just need to create a Combobox to lookup each of the 5 values required.
    Attached Thumbnails Attached Thumbnails EquipTrak.PNG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #48
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by mike60smart View Post
    Hi
    I am a little puzzled with the construction of your frmEquipTrack.

    The Form is based on a query "EquipTrack_Qry" which has the "tblEquip" plus all 5 of the Lookup Tables needed to retrieve specific data.

    You do not need these 5 tables in the query that this form is based on.

    On the Form you just need to create a Combobox to lookup each of the 5 values required.

    I have the five look up tables as part of the design because that is the only way that I can get the values of the second field on the tblEquipCat table to show up on the combo box. When a use clicks on the combo box I want them to see a list of things like VTR, Decoder, Encoder etc. instead of 1,2,3, etc.

  4. #49
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    See the modified frmEquipTrack that opens on startup. This form is based on tblEquip

    If you also look at the Form "frmEquip" this shows you the Equipment together with Maintenance dates in a Subform
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #50
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you moving off the record? That should be the only way you get unwanted records. If in the pic of your table I could see the editing icon for the record selector (far left) I'd say you're looking at an unsaved record (one that is in the process of being created). One solution would be to use form BeforeUpdate event to cancel or not. Another would be to ensure that whatever companion/related fields to this selection are required. If not, you'll get a prompt and won't be allowed off of the record, which in turn won't save it. Esc would cancel the record, but if you think that's not intuitive enough for users, you can buttons to save or not, or trap the error and present your own messages with response options. Many ways to prevent this and it has nothing to do with the combo properties except for one - control source property. If there isn't one, it's not bound so this doesn't happen. If there is, this is just the way it has always worked.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #51
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    It doesn't matter if I move from a record or save a record first. I am still writing values to my tables that I don't need.

    Click image for larger version. 

Name:	Record Select Icon.png 
Views:	13 
Size:	10.1 KB 
ID:	49423


    When I click on the arrow with the start I start to generate a new record.

    Then when I select a value that is already on my tblEquipCat table instead of seeing the ID that should already be on my tblEquipCat table, I am seeing a different number.

    When I click the left or write arrow on the record select icon, I now have a new ID and EquipCat record on my tblEquipCat.

    Even when I use the save Icon at the top of the window to save I have new records added to my tables.

    Click image for larger version. 

Name:	Annotation 2023-01-04 145047.png 
Views:	13 
Size:	11.0 KB 
ID:	49424

    All of this is happening before I save or move from a record.

  7. #52
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It doesn't matter if I move from a record or save a record first. I am still writing values to my tables that I don't need.
    You're not understanding what I'm saying. If you move off a record, you just saved it. Using the record selector arrows is just one way to move off a record, and that's what you're doing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #53
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Micron View Post
    You're not understanding what I'm saying. If you move off a record, you just saved it. Using the record selector arrows is just one way to move off a record, and that's what you're doing.
    I am sorry for not understanding you what you were saying.

    I think the issue is happening before I move off a record. I will play around with the beforeupdate event and see what I can do to solve the issue.

  9. #54
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Okay, I think that I found a solution to the problem. I tried to uses the beforeupdate event on the form properties, category text box properties and the combo box properties to no avail. I went back to @mike60smart's question about why my original query included all of my look up tables. It occurred to me that I might be trying to select the equipment category in the wrong place. I was trying to select the category on the tblEquipCat table that table is lined to the tblEquip Table so I am pretty sure that was my first mistake. The second mistake was including all of the fields on the all of the look up table which made a giant loop where I would change a value on the tblEquipCat access would add a new record to tblEquipCat table because I was making a change to that table rather than getting the value from the look up table.

    So to fix the issue I created a new query that was just based on the tblEquip and added all the field of the table to the query

    Click image for larger version. 

Name:	newqry.png 
Views:	8 
Size:	13.0 KB 
ID:	49445

    Then I created a form based on that query and converted the category text box on the form to a combo box. I set the column count to 2 and then set the column width as 0",1" so that only the EquipCat field from the tblEquipCat table shows up when you select the combo box.


    I set the Row Source properties of the combo box to

    Code:
     SELECT ID, EquipCat, ID FROM tblEquipCat ORDER BY ID;
    and I left the the combo box bound to column 1.

    Now, when I generate a new record and select the equipment category I am grabbing the value from the tblEquipCat table and making the change on the main form instead of trying to grab the value from the tblEquipCat table and writing that value to the tblEquipCat table.

    I repeated this process for each text box that I converted to a combo box.
    Attached Thumbnails Attached Thumbnails Column Count-Widths.png   Bound Column and Row Source.png  

  10. #55
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    I would base the form on the actual table
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Calculate Date based on Past Date and Frequency
    By jchandler88 in forum Queries
    Replies: 9
    Last Post: 10-01-2018, 07:15 PM
  2. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  3. Replies: 1
    Last Post: 11-26-2013, 09:25 AM
  4. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  5. Replies: 34
    Last Post: 12-01-2011, 08:18 AM

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