Results 1 to 13 of 13
  1. #1
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33

    Master form calls sub form item, links via combo box and updates yes no value.

    Hi



    I hope this is easy to understand.
    I have a From Called FrmJobs and It updates a table called tblJobs
    I have another table called tblSeals

    What I want to do is in my FrmJobs I want to look up a Seal from tblSeals using a combo box, select the unique seal number value stored in field BoltNum and the toggle box called BoltUsed turn to True so that my Table Jobs now links a specific JobID to a Specific BoltNum in tblSeals.

    I just don't know enough to do this. I have however got a query running that lists all bolts where the BoltUsed value = False so everytime I add a new job and select a new bolt, that bolt (each has a very unique ID in the world) it never able to be selected again.

    Any ideas?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    How does setting BoltUsed link JobID to specific bolt? if you want to link records then need to save a key value somewhere. Such as JobID into tblSeals.
    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.

  3. #3
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33
    The primary Key of the Jobs links to a field in Tabe Seals That is linked to a field called Outbound Seals, so the relationsip has been established.

    This is going to be used by some not very techy staff so I want them to click around as little as possible and I think this is the best way of doing it.

    I just wish I had time to learn more VBA and be less dependent on others who know this stuff.

    Thanks for your help by the way.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Then the BoltUsed field seems redundant. If Seals record has a JobID then it is used.

    If a bolt already has a JobID then don't list in combobox.

    What am I not understanding?
    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.

  5. #5
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33
    I suppose I dont understand...

    The bolt first has to appear in the list to be selected, then it is selected and when I go back to the list to add another, the original bolt still shows. I cant workout why. So I added a yes no option. and thought if the user selects a bolt, then when they save the or close the form, the yes no ticks to true.

    Its supposed to be a unique field but its always in my list.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Apply filter to combobox RowSource SQL to exclude bolts that have JobID. After a bolt is selected and JobID assigned, combobox must be requeried.

    Exactly how do you assign JobID to bolt record? What is the code to accomplish this?
    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.

  7. #7
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33
    Hi so I have gone back to this and have done the following.
    Table1. = Jobs with fields JobsId, and associated fields
    Table2 = Seals which June helped me populate. It has SealsID and JobsID as a foreign key as there is one Job to many seals (actually only 2)
    However the way we need to enter the data is by having a form that Fills in the information for Table1.
    We need a combobox that lists the Seals
    I select a seal and it updates a link between table 1 and table 2.
    However this is where I am stuck because I need that seal to no longer be in the combo box when i add a new Job
    I need ideas on how to link this up.
    Practicing it I have a form that has the fields for table 1, and a combo box for seals table table 2, but even when using the wizard the wizard wants to save the values in a foreign location in table 1, but when i think i want is to have a value in seals table that goes from false to true so i can have my query requery and list seals with a value of false.

    I have no code as I do not yet know how to code, but am slowly learning the basics

    Suggestions please.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    Normal approach would be a form bound to Table2 and combobox to select JobID and save as foreign key into Table2.

    Since you have form bound to Table1, will need code to UPDATE Table2 with JobID. But should first commit job record to table.
    Real trick is figuring out what event to use. Probably combobox AfterUpdate.
    VBA like:

    If Me.Dirty Then Me.Dirty = False
    CurrentDb.Execute "UPDATE Table2 SET JobID = " & Me.JobID & " WHERE SealID = " & Me.cbxSeal
    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
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33
    I think things are almost moving in the right direction.

    If Me.Dirty Then Me.Dirty = False
    CurrentDb.Execute "UPDATE [Seals] {my table 2} SET SealsJobID {my Foreign Key in table 2} = " & Me.JobsID {Primary key in my current form & table 1} & " WHERE SealsID {primary Key in table 2} = " & Me.ComSealNum {Value in combo box}

    However I get a Runtime error 3061 = Too few parameters Expected 1

    Any ideas?

  10. #10
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    393
    Hi
    **EDIT Ignore my post as I miss-read the issue
    Brackets are wrong - This {my table 2} should be This [my table 2]

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,629
    I think text in {} are intended to be explanatory notes in post, not in actual code - I hope.

    Post your SQL string exactly as it is in code, without the {} stuff. What I can see should work.

    What event did you put code in?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  12. #12
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    33
    If Me.Dirty Then Me.Dirty = False
    CurrentDb.Execute "UPDATE Seals SET SealsJobID = " & Me.JobsID & " WHERE SealsID = " & Me.ComSealNum

    I have tried it after update and got focus etc, but same result.

    Regards


  13. #13
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    393
    Hi

    Best way to resolve this is if you can upload a zipped copy of the Db

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

Similar Threads

  1. Replies: 11
    Last Post: 04-08-2020, 05:43 AM
  2. Replies: 11
    Last Post: 07-10-2016, 10:25 PM
  3. Replies: 4
    Last Post: 02-07-2013, 04:11 PM
  4. Combo Box on form updates unintentionally
    By zipmaster07 in forum Forms
    Replies: 2
    Last Post: 03-06-2012, 10:21 PM
  5. Combo-box to select item to edit in Form
    By DHavokD in forum Forms
    Replies: 7
    Last Post: 06-05-2009, 01:39 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 - Senior Forums