Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 40

Combo box option depending on previous combo box value & having to draw data from diff data table

  1. #16
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    In what sense do you not like the table structure? What are your opinions? I structured it like that because there would be a lot of parts and I dont want the first combo box to be cluttered with too many options.



    What did you do to fix it? My previous file had the same code as yours but it doesnt work. Is it because of the relationship? or is it because of my naming convention?

  2. #17
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    How do I filter the values of another table (More details like Engine model and countires and seatbelt model and related countries) based on the last combo box selection?

    Because based on my last combo box value Eg: Engine >>> Mazda 3 Engine. I want to print a report of all countries that have cars that carry Mazda 3 engine.

    Or if I choose windscreen >>>> Takata seatbelts model 1, I want to print a report of all countries that have cars that carry Takata seatbelt model 1

  3. #18
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    In what sense do you not like the table structure? What are your opinions?
    I didn't want to make too many changes to your dB.. after all, it is your dB.
    My 'opinion' (my style) is
    - I use an autonumber type field as the PK field for 99.9% of my tables.
    - I use a suffix of "_PK"/"_FK" for the PK and FK fields.

    So I made just enough changes to get the combo boxes to work, hopefully the way you wanted..

    See Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    See the sub headings: "Use a Meaningless Primary Key" and "Only Use One Numeric Field as the Primary Key"
    Read this site many times..... (I am up to 40+ times)

    You might also see
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers



    How do I filter the values of another table (More details like Engine model and countires and seatbelt model and related countries) based on the last combo box selection?
    Set up another combo box like the dB I posted.. Sorry, I can't give a better answer because I don't know the table structures/data involved in your current dB.


    The report would need a query that is filtered by the combo box selections.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #19
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    AHHH i see. BEcause I thought I changed most of my primary key to autonumber already, maybe I missed out on some tables

    How do I filter the values of another table (More details like Engine model and countires and seatbelt model and related countries) based on the last combo box selection?

    Because based on my last combo box value Eg: Engine >>> Mazda 3 Engine. I want to print a report of all countries that have cars that carry Mazda 3 engine.

    Or if I choose windscreen >>>> Takata seatbelts model 1, I want to print a report of all countries that have cars that carry Takata seatbelt model 1
    Because based on the previous combobox selection Takata Seatbelt model 1, I want to print the countries that have cars that carry Takata seatbelt model 1. I have attached a picture of the datatable and the form. I think I am not googling the right term, cant seem to find the right method to do it.


    Click image for larger version. 

Name:	Combobox to field printing.JPG 
Views:	10 
Size:	74.6 KB 
ID:	27326


    Click image for larger version. 

Name:	Combobox to field printing 2.JPG 
Views:	10 
Size:	162.9 KB 
ID:	27328
    I want to be able to show the sites that have RB211 C in the last combobox before I hit the print button and print the individual sites that is being selected. The black circle is the Sites that I want listed with the RB211G Value
    Attached Thumbnails Attached Thumbnails Combobox to field printing 2.JPG  

  5. #20
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    The dB I have does not have the fields you have circled in table "SiteDetails".
    Would you post the dB you are working from (the dB the pictures are from?) or put it on your drive.google.com site?


    "Combo6" (in the picture above) displays "Gas Generator". What is the name of the field that has "Gas Generator" as an entry (record)?
    "Combo8" (in the picture above) displays "RB211C". That is from the field "Engine"
    "Sites" (combo box) would be from the field "Sites".
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #21
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Hey thanks for the help. I managed to do a workaround. But now I am trying to have the 2 combobox value selection to filter the reports that are being printed.

    Currently what I have is 3 main fields (Engine, Power Turbine, Driven Equipment) in the report that filters the report to be printed based on the combobox value (ComboboxMainPartsD) selected.

    DoCmd.OpenReport "SiteDetails", acViewPreview, , "[Engine]='" & Me!ComboMainPartsD & "' Or [Power Turbine]='" & Me.ComboMainPartsD & "' Or [Driven Equipment]='" & Me.ComboMainPartsD & "'"
    What I want to do is to add another ComboboxMainPartsD (ComboboxMainPartsD2) to further filter the report.

    Eg, I have a lot of sites that have RB211 engines, but inside those sites they might have RT56 or RT48 Power Turbines. So I want sites with just RB211 Engines and RT56 Power Turbine.

    DoCmd.OpenReport "SiteDetails", acViewPreview, , "[Engine] or [Power Turbine] or [Driven Equipment] = '" & Me!ComboMainPartsD And Me.ComboMainPartsD2 & "'"


    https://drive.google.com/open?id=0B4...jgzc3pQSzJPam8

  7. #22
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    I'm kind of lost and frustrated with the dB! I asked for the dB you are working with and you post a dB with at least 20 field removed from table "SiteDetails".
    This is not helpful.
    Plus there are a lot of things that should be corrected. Having a PK field named "No" is problematic. "No" is a reserved word in Access (actually JET).

    Why are there tables "MalaysiaEMEPMI" and "ThailandPTTSites"???


    It is not real clear what/how you want to filter the report by: you want to pick a field (Engine, Power turbine, Gearbox...), then pick an engine type (RG211 C, Ruston, Avon,...) , then pick a site?
    Then next report you want to pick a field (Gearbox), then pick a Gearbox type (Maag, Philadelphia Gear,....), then you may or may not pick a site?
    What you want is not clear!


    This will definitely not work! You have to explicitly declare a field and a value.
    Code:
    DoCmd.OpenReport "SiteDetails", acViewPreview, ,  "[Engine] or [Power  Turbine] or [Driven Equipment] = '" & Me!ComboMainPartsD And  Me.ComboMainPartsD2 & "'"

    Code:
    DoCmd.OpenReport "SiteDetails", acViewPreview, , "[Engine]='" &  Me!ComboMainPartsD & "' Or [Power Turbine]='" &  Me.ComboMainPartsD & "' Or [Driven Equipment]='" &  Me.ComboMainPartsD & "'"
    Lets give values to the combo boxes>
    For "Engine", you select "RB211 C". This will return 3 records
    For "Power Turbine", you select "RT56". This will return 12 records, but only 3 records also have Engine = "RB211 C".
    For "Driven Equipment", you select "Ideal Electric". This will return 6 records.

    So your report will have 18 records, but not one record will have all 3 criteria. This is because of the "OR" between the terms.
    This is what the criteria looks like
    Code:
    SELECT SiteDetails.Engine, SiteDetails.[Power Turbine], SiteDetails.[Driven Equipment]
    FROM SiteDetails
    WHERE (((SiteDetails.Engine)="RB211 C")) OR (((SiteDetails.[Power Turbine])="RT56")) OR (((SiteDetails.[Driven Equipment])="Ideal Electric"));
    If you changed the criteria to
    Code:
    DoCmd.OpenReport "SiteDetails", acViewPreview, , "[Engine]='" &  Me!ComboMainPartsD & "' AND [Power Turbine]='" &  Me.ComboMainPartsD & "' AND [Driven Equipment]='" &  Me.ComboMainPartsD & "'"
    with the same criteria as above, 0 records would be returned.
    Here is the SQL
    Code:
    SELECT SiteDetails.Engine, SiteDetails.[Power Turbine], SiteDetails.[Driven Equipment]
    FROM SiteDetails
    WHERE (((SiteDetails.Engine)="RB211 C")) AND (((SiteDetails.[Power Turbine])="RT56")) AND (((SiteDetails.[Driven Equipment])="Ideal Electric"));
    Do you see the difference between using "OR" and "AND"?


    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #23
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Pm-ed you already. Thanks for your help!

  9. #24
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    Look at this...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #25
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    More changes... (if I did this right)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #26
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    More changes... (if I did this right)
    Thanks! It works!

    What did you do to make it work? Like what I went wrong?

    Where do AuditChanges function get the active form name? Because previously I used Screen.ActiveForms.Name to get it, but now you declare pFormName but I dont see pFormName drawing the current form information from anywhere.

    Didnt realise I exited Function after printing AuditErr. Was that the main stumbling block?

    Dont mind add a prompt for users to insert their reason for editing/delete the "Site Details"?

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    I made a lot of changes...

    Learning experience: Print out your old version of the code and the code I just posted....compare line by line the two versions.
    Kinda like "Where is Waldo?"


    Where do AuditChanges function get the active form name? Because previously I used Screen.ActiveForms.Name to get it, but now you declare pFormName but I dont see pFormName drawing the current form information from anywhere
    I changed the "auditChanges" procedure header to also pass the calling form name as an argument.
    Code:
    Public Function AuditChanges(pRecordID As Long, pUserAction As String, pFormName As String)
    In the form "AddingSites" before update code, I added "Me.Name" (the current form name in Blue below)
    Code:
    Private Sub Form_BeforeUpdate(cancel As Integer)
      '  Me!DateModified = Now()   '<<--old ... BAD
        Me!tbDateModified = Now()   '<< found an error. Find this Sub and change the control name to this
    
        If Me.NewRecord Then
            Call AuditChanges(Me.Text196, "New", Me.Name)
        Else
            Call AuditChanges(Me.Text196, "Edit", Me.Name)
        End If
    End Sub

    Oh, one more thing. You had code in the form after update event that would set the DateModified field (I renamed the control to "tbDateModified" (the "tb" prefix is for "text box").
    So the before update event would fire, the data would be saved, then the after update event fires, which made the form dirty again, which would give the error "Cannot go to the specified record" error. Vicious circle (endless loop)!

    I moved the line to the before update event. (see the code above where I say there is an error).
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #28
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    I made a lot of changes...

    Learning experience: Print out your old version of the code and the code I just posted....compare line by line the two versions.
    Kinda like "Where is Waldo?"



    I changed the "auditChanges" procedure header to also pass the calling form name as an argument.
    Code:
    Public Function AuditChanges(pRecordID As Long, pUserAction As String, pFormName As String)
    In the form "AddingSites" before update code, I added "Me.Name" (the current form name in Blue below)
    Code:
    Private Sub Form_BeforeUpdate(cancel As Integer)
      '  Me!DateModified = Now()   '<<--old ... BAD
        Me!tbDateModified = Now()   '<< found an error. Find this Sub and change the control name to this
    
        If Me.NewRecord Then
            Call AuditChanges(Me.Text196, "New", Me.Name)
        Else
            Call AuditChanges(Me.Text196, "Edit", Me.Name)
        End If
    End Sub

    Oh, one more thing. You had code in the form after update event that would set the DateModified field (I renamed the control to "tbDateModified" (the "tb" prefix is for "text box").
    So the before update event would fire, the data would be saved, then the after update event fires, which made the form dirty again, which would give the error "Cannot go to the specified record" error. Vicious circle (endless loop)!

    I moved the line to the before update event. (see the code above where I say there is an error).
    Haha Many thanks for your help! Your skills are awesome, must have spend quite a good deal of time to be an expert on it.

    I am trying to make it a ACCDE file but there is a error.

    "Microsoft Access was unable to create the .accde, .mde., or .ade file.

    I tried to fix it but the compilation error is at the "AuditChanges" part of the VBA code. I do not see that you made "AuditChanges" delete event function optional. Whats the issue here?

    Compile Error: Argument Not Optional

    Private Sub DeleteRecord_Click()


    Dim rst As Recordset
    Dim strCopy, strSQL As String
    Dim answer As String


    If IsNull(Me.Client) Then
    MsgBox "No Record to delete"


    Else
    answer = MsgBox("Are you sure you want to delete this record)", vbYesNo + vbCritical + vbDefaultButton2, "Delete Confirmation")
    If answer = vbYes Then
    Call AuditChanges("ID", "Delete")
    strCopy = "Insert Into Deletedcustomer select SiteDetails.* from SiteDetails where (SiteDetails.ID = " & Me.ID & ");"
    strSQL = "delete * from SiteDetails where ID= " & Me.ID


    DoCmd.SetWarnings False
    DoCmd.RunSQL strCopy
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True




    DoCmd.GoToRecord , , acNewRec


    End If
    End If
    End Sub

    AuditChanges (Module 1)
    Case "Delete"
    With rst
    .AddNew
    !DateTime = Now()
    !UserName = UserLogin
    !FormName = pFormName
    !Action = pUserAction
    !RecordID = pRecordID
    !Reason = gstrReason
    .Update
    End With

    "Microsoft Access was unable to create the .accde, .mde., or .ade file.

  14. #29
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,258
    I didn't look at that part of the code.

    You could modify that line like this:
    Code:
    Call AuditChanges("ID", "Delete", Me.Name)
    ================================================== =============
    I can't look at the dB right now, but I will later. I think I saw two subs named "AuditChanges".... (not a good idea)

    ================================================== =============
    Other items:

    Code:
    Private Sub DeleteRecord_Click()
    
        Dim rst As Recordset
        Dim strCopy, strSQL As String
        Dim answer As String
    The line "Dim rst As Recordset"
    should be "Dim rst As DAO.Recordset"


    In this line "Dim strCopy, strSQL As String", "strSQL" is declared as a string, "strCopy" is a variant. In VBA, you have to explicitly declare variables.
    Should be
    Code:
     ' Dim strCopy, strSQL As String
    Dim strCopy As String, strSQL As String

    The return value from the message box is an integer (vbOK = 1, vbYes = 6, vbNo = 7), the variable "answer" should be declared as an Integer, not as a String.
    Code:
    '  Dim answer As String
    Dim answer As Integer
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  15. #30
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Thanks! I fixed the errors above.

    1st fix: But I cant fix the Me.ID. They keep giving me back Method or data member not found. Even when I changed it to AuditTrailID, same error appears.

    2nd fix: I changed it to Me!ID but now I get a Runtime error Type 13: type mismatched, Pointing at the line >>> Call AuditChanges("AuditTrailID", "Delete", Me.Name). Changed AuditTrailID to ID and to RecordID also returns the same error. I am lost already

    Private Sub DeleteRecord_Click()


    Dim rst As DAO.Recordset
    Dim strCopy As String, strSQL As String
    Dim answer As Integer


    If IsNull(Me.Client) Then
    MsgBox "No Record to delete"


    Else
    answer = MsgBox("Are you sure you want to delete this record)", vbYesNo + vbCritical + vbDefaultButton2, "Delete Confirmation")
    If answer = vbYes Then
    Call AuditChanges("AuditTrailID", "Delete", Me.Name)
    strCopy = "Insert Into Deletedcustomer select SiteDetails.* from SiteDetails where (SiteDetails.ID = " & Me.ID & ");"
    strSQL = "delete * from SiteDetails where ID= " & Me.ID


    DoCmd.SetWarnings False
    DoCmd.RunSQL strCopy
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True



    DoCmd.GoToRecord , , acNewRec


    End If
    End If
    End Sub

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

Similar Threads

  1. Replies: 5
    Last Post: 12-01-2015, 02:22 PM
  2. Replies: 1
    Last Post: 09-30-2015, 12:58 PM
  3. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  4. Replies: 2
    Last Post: 01-06-2014, 04:22 PM
  5. Display table data depending on selected data
    By swavemeisterg in forum Forms
    Replies: 7
    Last Post: 07-30-2013, 03:43 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
  •  
Tech Forums: Microsoft Office Forums