Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    I use the frm1Main_DriverMetricDetails to call frmSub1_DriverMetricDetails

    frm1Main_DriverMetricDetails pulls the Driver related info from tbl2_Drivers

    frm1Sub_DriverMetricDetails is used to Insert data into tbl3_MetricDetails using the Driver info with Driver_ID being the Primary Key.
    frm1Sub_DriverMetricDetails also pulls the Metric_IDs and Flag_IDs from their respective tables tbl4_MetricIDs and tbl5_FlagIDs.

    What I want is, when I open frm1Main_DriverMetricDetails, the frm1Sub_DriverMetricDetails should "load" all the MetricIDs (1-11) from tbl4_MetricIDs and only thing a user has to do is to input the FlagIDs

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you want to create a batch of records associated with driver - all drivers or just one particular driver?

    Can be done with INSERT SELECT sql action constructed and executed in VBA.
    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. #18
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    All drivers .. following is the VBA code I am using right now.

    Foll pulls the existing records if any in the tbl3_MetricDetails
    ........................
    Private Sub cboDriver_ID_AfterUpdate()
    With Me.RecordsetClone
    .FindFirst "Driver_ID='" & Me.cboDriver_ID & "'"
    If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With
    Me.Record_Date = Null
    Me.cboDriver_ID.SetFocus
    End Sub


    Foll Inserts new Records into tbl3_MetricDetails
    ..........................
    Private Sub Record_Date_AfterUpdate()

    CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT '" & Me.Record_Date & "' AS RD, '" & Me.cboDriver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"

    Me.frmSub_MetricDetails.Requery

    End Sub

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That SQL works?

    Date/Time fields use # delimiter not apostrophe. Number fields do not use any delimiters.
    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
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    Yes it works because the .. Record_Date is pulled as a static field from the frm1Main_MetricDetails, so I don't need to use # for Date field.

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is a 'static' field - UNBOUND textbox?

    Okay, but that is for only one driver. If you want to 'batch' create records for all drivers and metrics for the given date, need a Cartesian relationship. That is a query with no JOIN clause. Every record of each table associates with every record of other table.

    CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT " & Me.Record_Date & ", Driver_ID, Metric_ID FROM tbl2_Drivers, tbl4_MetricIDs"
    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
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    I want to Batch create records for 1 driver at a time. Let me explain the process ...

    1. User opens frm1Main_DriverMetricDetails which also has the frm1Sub_DriverMetricDetails in it.
    2. User clicks on the Driver ID Combobox to select a Driver.
    3. User clicks on Date to select Record_Date.
    4. When the above happens, the frm1Sub_DriverMetricDetails should "load" all the 11 metrics from tbl4_metricIDs and also their relevant Flag_IDs if any from the tbl3_MetricDetails.
    5. User should only have to update/insert Flag_IDs in the frm1Sub_DriverMetricDetails.

    Kind of if I have a simple paper form with 2 columns asking a user to indicate if Food & Drink required.
    The Form should have Food & Drink already printed and the user only has to mark Y or N under the Answer column using a pencil.
    e.g.

    Menu Item Answer
    Food Y
    Drink N

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Alright, you have the code for both options - 1 driver or all drivers - to create a batch of new records in tbl3_MetricDetails.

    If you want to retrieve existing records for edit, that is a different matter.

    Exactly what is the issue?
    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
    Rangerguy2000's Avatar
    Rangerguy2000 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    Hamilton, ON, Canada
    Posts
    32
    1 driver at a time.

    Form (main+sub) should "load" (sorry for using this term) all the Metric_IDs from tb4_MetricIDs so that the user doesn't have to type the Metric_ID everytime.
    Also If I want to increase/decrease the # of Metric_IDs in the future, I just update the tbl4_MetricIDs and the user sees an updated list.

    When the above form loads, the Metric_IDs should pull their relevant Flag_ID from the tbl3_MetricDetails if any present and be able to Edit them if needed as well.
    And if no Flag_ID present, then the user should be able to enter new Flag_ID as a new entry.

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    First, to 'load' all the metric IDs for specified driver/date must create records in tbl3_MetricDetails (INSERT SELECT). Then, for user to edit the flags, open (or requery) form to that set of records for specified driver/date. What exactly about this process and the code already provided is not clear?

    Likely will need code that checks if the set of records already exists and if not, create them (INSERT SELECT) or if records exist just apply filter criteria.
    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.

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

Similar Threads

  1. Replies: 9
    Last Post: 07-07-2016, 02:34 AM
  2. Replies: 9
    Last Post: 12-17-2014, 06:13 PM
  3. Calculations based columns in a entry form
    By Jamescdawson in forum Access
    Replies: 8
    Last Post: 06-11-2012, 07:09 AM
  4. Incorrect Calculations in Report
    By Bridiewms in forum Reports
    Replies: 6
    Last Post: 01-30-2012, 10:28 AM
  5. Replies: 22
    Last Post: 03-15-2011, 07:17 AM

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