Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So first the record in Bid table must be created and committed to table. Is this done on the main form? Then in the subform you select Service_ID# values? Also need to make sure the last entered Service_ID# record is committed to table.

    The suggested SQL will insert multiple records. It will find all records associated with the specified Request_ID# and copy the associate Service_ID# values to Bid-Services along with the BID_ID#.



    So, yes, your modified SQL has the right idea. It is now a matter of making sure records are committed and then referencing the correct field and control names. And this is dictated by where the code is placed - main form button click event?

    At this point, think I would have to review your db to help further. If you want to provide, follow instructions at bottom of my post. If zip still too large can upload to a fileshare site and post link to file. I recommend Box.com.
    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.

  2. #17
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    Ah, okay great.
    I believe the Bid ID# gets saved only after the form is completed and saved via > navigation button arrow

    Here is my db: https://www.dropbox.com/sh/2a4xubnds...6U_MWjqka?dl=0
    If it's too complicated I will just keep it set up such that the user inputs the Bid-Services manually through reference. I'm just hopeful after all this time searching, requesting, and getting help was not in vain to be able to get the "#2" thing figured out!

  3. #18
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    Are you able to view the Access2016 format? It won't let me save in 2000 or 2003 format

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you including Request-Services in the subform? Subform is for entry of records to Bid-services. INNER JOIN requires associated records on both sides of the join for records to be returned. A BOUND form can be used to enter records for only one table. Including other tables in the form RecordSource can prevent new records, as happens in your form. Just occurred to me maybe prefer to prevent manually adding new record. Had to remove Request and Request-Services tables from the query. You didn't show any fields from those tables on the subform anyway.

    Should give control a name different from the field or form bound to, such as tbxBID and ctrBidServices and cbxSelReq. I changed names and this code works:
    Code:
    DoCmd.RunCommand acCmdSaveRecord
    CurrentDb.Execute "INSERT INTO [Bid-Services] ([Bid ID#], [Service ID#]) SELECT " & Me.tbxBID & " AS BID, [Service ID#] FROM [Request-Services] WHERE [Request ID#] = " & Me.cbxSelReq
    Me.ctrBidServices.Requery
    Can't emphasize enough that using spaces, punctuation/special characters (except for underscore) in names is very bad and will give you major headaches down the road. You should fix this before proceeding.
    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. #20
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    Interesting, interesting. It's very exciting after making your suggested changes to see the subform generate records and populating the Bid-Services table.

    I may have missed a step, but it seems that after choosing a selection from the combobox, and then making another selection, the DB just wants to add on to the subform versus refreshing it.

    Would you mind uploading your modified version to the folder so I can use as reference?
    https://www.dropbox.com/sh/2a4xubnds...6U_MWjqka?dl=0

    Thanks so much. In the time you spent helping me, I've learned more about access than during the many weeks of this course.

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I don't understand. Code is designed to add new records and refresh the subform with each selection from combobox. And that is exactly what it is doing. What else should it do?

    You might want to activate the vertical scroll bar on the subform.

    Also, revised my previous post as I was a little off-base about query structure.
    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. #22
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    Okay, I can only assume that I'm overlooking something if you were able to get it to work. I believe you've given all knowledge needed for me to be able to get my db to function properly, as you did.
    Consider this thread marked solved!
    Thank you so so much for your help. It truly is amazing how willing to help you were with no strings attached. *e-hug* *e-brofist*

    My assignment deadline has long passed a week ago, and I ended up submitting a non-functioning db which will result in a failing grade, but I needed to learn for myself for pride how to finish this damn db the way I had initially planned. If only I had started this process sooner... Regardless, I appreciate your help in conquering my goal.

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Provide your revised db and I will see where you went astray from the instructions.
    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. #24
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    My Gosh, this is frustrating.
    Sorry to bother again. I've uploaded what I've gotten so far to : https://www.dropbox.com/sh/2a4xubnds...6U_MWjqka?dl=0

    I changed the subform queries to right join as you instructed. Does this still maintain a many-to-many relationship between Services:Request and Bid:Request ?

    Because I was having a problem where selecting different requests were only adding records to the subform, I deleted all of the BidServices records, and this caused the attributes of some of the Service table records to get deleted as well? So I had to manually input the categories, service names, and daily rate. I'm not sure if this is what caused it, but both of my linking tables (RequestServices and BidServices) got deleted as well in the relationships.

    I'm still having trouble with the queries refreshing, so I thought perhaps using a bookmark could work.

    And I don't understand why my BidServices subform is loading in datasheet view..all of a sudden?

    So close, yet so far...

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The subform container SourceObject property was changed to the query, change it back to the form.

    Get rid of the bookmark stuff. Not referencing the combobox in the SQL concatenation. Fix the VBA to:

    Code:
    DoCmd.RunCommand acCmdSaveRecord
    CurrentDb.Execute "INSERT INTO [BidServices] ([Bid_ID], [Service_ID]) SELECT " & Me.txtBoxBid_ID & " AS BID_ID, [Service_ID] FROM [RequestServices] WHERE [Request_ID] = " & Me.cboReqID
    Me.subfrmBidServices.Form.Requery
    Big improvement in naming convention. Means use of [] not so critical.
    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.

  11. #26
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    Yes, thank you. I took your advice and changed the names; it wasn't as big a pain as I thought.

    Everything's looking good, except that once a selection is made in the combobox, but then changed, the records that were created for the first selection remain.

    Any way we can gaurantee the refresh? Perhaps adding to the WHERE clause?

    BTW I only put your corrected code in the afterupdate event of the combobox.

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The combobox AfterUpdate event is appropriate location for the code.

    Yes, the records made in the first selection remain as well as the second and third etc. That is how the code works. It is ADDING records, not overwriting. So the refresh is working correctly. I did not understand the records need to be replaced instead of added to. If you want them replaced, that will require a DELETE action SQL to be run before the INSERT action so the existing set will be removed.

    CurrentDb.Execute "DELETE FROM BidServices WHERE Bid_ID = " & Me.txtBoxBid_ID

    Manually or otherwise deleting records from BidServices should not cause records in Services to delete. As you can see by this code.

    Field [CS Employee_ID] in Bid table still has a space in name.

    I removed my comments from earlier post about needing to set query to RIGHT JOIN because I realized that was not really accurate, but won't hurt.
    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.

  13. #28
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    !!! WOW. This is so exciting. Just one crucial line of code to make everything run as I hoped.
    Sorry to confuse you about the refresh vs replace.

    My brain is dead at the moment as it's 5am here, so I need a little shut eye.

    Thank you for everything. <3 I'm truly grateful sir June7
    You are very kind and giving. I'd like to be able to thank you if there's a way outside of $, although I do owe you such.

  14. #29
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Just glad it's working as you wanted.

    Also, the subform textboxes bound to fields in Services table should be set as Locked Yes and TabStop No because these should not be editable. That would change values in the Services table. These fields should just be displayed for information. If you don't want manual edits of the Bid_ID and Service_ID values, then do the same for those textboxes.

    And if you want to prevent manually adding and deleting records, the form AllowAdditions and AllowDeletions properties can be set to No.
    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.

  15. #30
    Essel30 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Apr 2017
    Posts
    17
    What is the simplest fix so that if the BidServices has been filled for a particular bid, where fields such as estimated_days and markdown have been saved to the table, the user cannot delete these records if he were to change the combobox selection?

    I tried putting code:
    Code:
    If IsNull(Me.subfrmBidServices.Form.Controls(Estimated_Days)) Then
    CurrentDb.Execute "DELETE FROM BidServices WHERE Bid_ID = " & Me.txtBoxBid_ID
    CurrentDb.Execute "INSERT INTO [BidServices] ([Bid_ID], [Service_ID]) SELECT " & Me.txtBoxBid_ID & " AS BID_ID, [Service_ID] FROM [RequestServices] WHERE [Request_ID] = " & Me.cboReqID
    Me.subfrmBidServices.Form.Requery
    Else
    MsgBox "Clear Estimated Days before selecting different Request ID"
    End If
    but it seems glitchy

    The new issue I'm facing is that when the user is browsing the Bids in Form View, if he were to accidentally change the combobox, the subform performs the DELETE and INSERT INTO, essentially deleting what was put for the estimated days and markdown.

    And this code doesn't seem to be effective in stopping this.

    EDIT:
    I believe I solved my issue by putting this code in the BeforeUpdate() event of the cbobox
    Code:
    Private Sub cboReqID_BeforeUpdate(Cancel As Integer)
    
    If Not Me.NewRecord Then
    MsgBox ("This can only be selected on  New Bids")
    Cancel = True
    Me!cboReqID.Undo
    Else
    End If
    End Sub
    Last edited by Essel30; 05-03-2017 at 12:16 AM.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-27-2014, 11:30 AM
  2. Replies: 1
    Last Post: 03-22-2013, 03:33 AM
  3. Replies: 1
    Last Post: 08-13-2011, 12:03 AM
  4. Populate table after DLookup is performed
    By OMGsh Y did I say Yes in forum Forms
    Replies: 16
    Last Post: 03-21-2011, 11:25 AM
  5. Replies: 0
    Last Post: 05-12-2010, 10:08 PM

Tags for this Thread

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