Solved Loop through using OpenArg on main form and 2 sub forms deep (1 Viewer)

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Hello

I have spent the best part of the day on lockdown trying to sort an issue out - what I want to do is so simple, yet seems the most complicated thing I've come across so far in VBA. I have tried everything I can find online without any luck! So any help would be great. I am at desperation point now to be honest.

Anyway, I have a contact form linked to an order history subform (parent and child) linked to a products ordered subform (parent and child) all run off a query. I want most things to be run off one form to make it more user friendly. Not a big fan of overlapping forms. I have a main menu with a listbox with the records relating to the query. I simply want the correct record to open up when I double click it. I have the code below already which loads up the correct information up to the first subform, but not the 2nd.

This is the vb in the listbox on the main menu.....

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
FindIDProcess = Me.StatusListBox.Column(0)
FindIDCustomer = Me.StatusListBox.Column(1)
FindIDSpec = Me.StatusListBox.Column(6)

DoCmd.OpenForm "F_Contact_Details", , , "[IDCONTACT]=forms![F_MainMenu]!FindIDCustomer", , , FindIDProcess
Forms!F_Contact_Details!ContactViewedAt = Now()
Me.RecentContactsListBox.Requery
End If

This is then in the 'on load' event on the parent form with the 2 subforms
Code:
Dim strOpenArgs() As String
If Not IsNull(Me.OpenArgs) Then
    Me.SF_Quote.SetFocus
    Me.SF_Quote.Form!IDORDERPROCESS.SetFocus
    DoCmd.FindRecord Me.OpenArgs
    Me.SF_Quote.Form!Proses.SetFocus
    Forms![F_Contact_Details]![SF_Quote]![SF_DrumOrderSpec].Form!OtherDrumsListBox.Requery
end if
I have tried the code below, but it returns an error that the record is locked
Code:
FindIDProcess = Me.StatusListBox.Column(0)
FindIDCustomer = Me.StatusListBox.Column(1)
FindIDSpec = Me.StatusListBox.Column(6)

DoCmd.OpenForm "F_Contact_Details", , , "[IDCONTACT]=forms![F_MainMenu]!FindIDCustomer", , , FindIDSpec
Forms!F_Contact_Details!ContactViewedAt = Now()
Me.RecentContactsListBox.Requery
End If

Dim strOpenArgs() As String
If Not IsNull(Me.OpenArgs) Then
    Me.SF_Quote!SF_DrumOrderSpec.SetFocus
    Me.SF_Quote!SF_DrumOrderSpec.Form!IDORDERSPEC.SetFocus
    DoCmd.FindRecord Me.OpenArgs
    Me.SF_Quote.Form!Proses.SetFocus
    Forms![F_Contact_Details]![SF_Quote]![SF_DrumOrderSpec].Form!OtherDrumsListBox.Requery
end if
Can anyone help at all? I'm not even sure how possible it is. If so, I will need to modify the design of the system. Thanks in advance.
 
Last edited by a moderator:

bastanu

AWF VIP
Local time
Today, 16:03
Joined
Apr 13, 2010
Messages
1,401
The two subforms should be linked to the main form by Master/Child fields so they should both bring up the right records when you load the correct record in the main form using DoCmd.OpenForm "F_Contact_Details", , , "[IDCONTACT]=forms![F_MainMenu]!FindIDCustomer"

Cheers,
Vlad
 

Minty

AWF VIP
Local time
Today, 23:03
Joined
Jul 26, 2013
Messages
10,355
A subform should be linked to the mainform by some key, so I'm not sure what wouldn't work?

You said the whole form is driven of one query, this makes little sense the subform records shouldn't be part of that query the child/master key setting will take care of that.

Edit - I see bastanu has beaten me to it - perhaps you post up a stripped-down version of your DB if our comments make no sense.
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Cheers for the quick reply guys. Yes, (my bad) they are linked through parent and child fields and not a query.

It does give me the records that are all linked. The issue I have is in the last form (2nd sub form) in the chain has a one to many relationship and when I load up the form from the main menu it populates the last form with the first record belonging to that contact, even though they could have bought 4 products. I can't access the other 4 unless I open up the form separately, but in an ideal world, I don't want to do that.

Thanks again BTW!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
The 2nd subform needs to be linked to the first subform, I believe. If that is the case you can do that in several ways. The most common is the hidden textbox updated on the on current event. See demo.
 

Attachments

  • LinkedSubforms.zip
    154.3 KB · Views: 101

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
The 2nd subform needs to be linked to the first subform, I believe. If that is the case you can do that in several ways. The most common is the hidden textbox updated on the on current event. See demo.

Thanks for this - The forms are linked. The difference between what you sent and what I have is that your second sub form default view is set up as a continuous form. I have mine set up as a single form in order to only see one of those records, depending on what the user has clicked in the listbox. Ideally would like to keep it that way. Is it possible?

Thanks again for the help, it really is appreciated!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
Yes. You can do the same trick with the hidden textbox. You would need to do it in the listbox afterupdate and also since you requery the list you would need to set the value then. However it would be a little easier to convert the listbox into a continuous form.
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Yes. You can do the same trick with the hidden textbox. You would need to do it in the listbox afterupdate and also since you requery the list you would need to set the value then. However it would be a little easier to convert the listbox into a continuous form.

Unfortunately, I cannot get it to work. I've wasted a whole day on it so far, not sure I am going to crack it!!!! I now get the error 'runtime error 2465 application-defined or object-defined error'. I can't see how it would work with what you're saying as I need the unique ID to open up a specific record - where am I putting that unique ID? If I put the link in the hidden text box, it's going to give me all the records based on the link from the first sub form? So just to clarify....

Master FormSub Form 1Sub Form 2
Link Master FieldIDCONTACTIDORDER
Link Child FieldID_CONTACTID_ORDER
Unique IDIDCONTACTIDORDERIDORDERSPEC

I want to open up a record with the unique ID 'IDORDERSPEC'. I can open up the unique ID for 'IDORDER by using the OpenArg which works really well. Just can't get the unique record for sub form 2....

Thanks again for the help.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
Any chance you can post a stripped down version. Usually if you can we can solve the issue very quick.
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
OK - here you go, sorry took a while to get it down to a good size. Take it easy on me though, just an amateur user who's trying to keep my customers and orders in check ;)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
Maybe I am missing something here, but is it not just the following in the the second subform control?
Master Fields: IDORDERPROCESS;ID_CUSTOMER
Child Fields: ID_ORDER;ID_CUSTOMER
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Yes, that all works fine, but when I click on a record from the listbox in the main menu form, it loads up all the records with IDORDERPROSESS (which can be multiple - see listbox in image attached). The first record in the listbox is the one displayed in the tab 'Cyffredinol' but I have no way of getting to the other two record below (unless I open up the SF_drumorderspec separately or have a continuous form, neither of which I don't really want to do). If I click on one, it goes back to the record it is currently on in the picture attached.

Appreciate your patients (y)
 

Attachments

  • Access problem.png
    Access problem.png
    130.6 KB · Views: 105
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
s, that all works fine, but when I click on a record from the listbox in the main menu form, it loads up all the records with IDORDERPROSESS (which can be multiple - see listbox in image attached).
Did you even try what I posted? This is my image.
Orders.jpg
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
A couple things to make your life easier.
1. Avoid ! whenever possible. You lose intellisense. Only use in queries and expressions and very very rare cases in vba.
2. Use variables so as not to repeat long references
3. You can use with end with when you set a lot of properties as well
4. Use select case when you have a lot of choices
Code:
Private Sub FirstDrumButton_Click()
    Me!SF_DrumOrderSpec.SetFocus
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!ID_CUSTOMER = Me.ID_CUSTOMER
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!ID_ORDER = Me.IDORDERPROCESS
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Cyffredinol.Enabled = True
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Cyffredinol.SetFocus
    Forms!F_Contact_Details!F_Quote.Form!ArbedButton.Enabled = True
    If Me.FirstDrumComboBox = "Snare Drum" Then
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Tannau.Enabled = True
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Snare Drum"
    Else
        If Me.FirstDrumComboBox = "Rack Tom Drum" Then
        Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Rack.Enabled = True
        Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Bass/Kick Drun"
        Else
            If Me.FirstDrumComboBox = "Floor Tom Drum" Then
            Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Floor.Enabled = True
            Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Rack Tom"
            Else
                If Me.FirstDrumComboBox = "Bass Drum" Then
                Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Bass.Enabled = True
                Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Floor Tom"
                End If
            End If
        End If
    End If

End Sub

to
Code:
Private Sub FirstDrumButton_Click()
    dim frm as access.form
    set frm = Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form
    Me!SF_DrumOrderSpec.SetFocus
    Frm.ID_CUSTOMER = Me.ID_CUSTOMER
    Frm.ID_ORDER = Me.IDORDERPROCESS
    DoCmd.RunCommand acCmdSaveRecord
    Frm.Cyffredinol.Enabled = True
    Frm.Cyffredinol.SetFocus
    Forms!F_Contact_Details!F_Quote.Form!ArbedButton.Enabled = True
    select case me.firstDrumbox
      case "Snare Drum"
        Frm.Tannau.Enabled = True
        Frm.TypeOfDrum = "Snare Drum
      Case ""Rack Tom Drum"
        Frm.Rack.Enabled = True
        Frm.TypeOfDrum = "Bass/Kick Drum"
      Case "Floor Tom Drum"
        Frm.Floor.Enabled = True
        Frm.TypeOfDrum = "Rack Tom"
      Case "Bass Drum"
         Frm.Bass.Enabled = True
         Frm.TypeOfDrum = "Floor Tom"
     end select
 End Sub

Easier to read and a lot easier to find errors.

Code:
Private Sub Priority_AfterUpdate()

    If Me.Priority = "1. Brys" Then
    Me.Priority.BackColor = vbRed
    Else
        If Me.Priority = "2. Uchel" Then
        Me.Priority.BackColor = RGB(255, 102, 0)
        Else
            If Me.Priority = "3. Canolig" Then
            Me.Priority.BackColor = vbYellow
            Else
                If Me.Priority = "4. Isel" Then
                Me.Priority.BackColor = vbGreen
                Else
                    If Me.Priority = "5. Dim" Then
                    Me.Priority.BackColor = vbWhite
                    End If
                End If
            End If
        End If
    End If

End Sub
to
Code:
Private Sub Priority_AfterUpdate()
    Select Case me.priority
      case "1. Brys"
        Me.Priority.BackColor = vbRed
      case "2. Uchel"
        Me.Priority.BackColor = RGB(255, 102, 0)
      case "3. Canolig" 
        Me.Priority.BackColor = vbYellow
      case "4. Isel"
        Me.Priority.BackColor = vbGreen
      case "5. Dim"
        Me.Priority.BackColor = vbWhite
    end select
End Sub
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
A couple things to make your life easier.
1. Avoid ! whenever possible. You lose intellisense. Only use in queries and expressions and very very rare cases in vba.
2. Use variables so as not to repeat long references
3. You can use with end with when you set a lot of properties as well
4. Use select case when you have a lot of choices
Code:
Private Sub FirstDrumButton_Click()
    Me!SF_DrumOrderSpec.SetFocus
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!ID_CUSTOMER = Me.ID_CUSTOMER
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!ID_ORDER = Me.IDORDERPROCESS
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Cyffredinol.Enabled = True
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Cyffredinol.SetFocus
    Forms!F_Contact_Details!F_Quote.Form!ArbedButton.Enabled = True
    If Me.FirstDrumComboBox = "Snare Drum" Then
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Tannau.Enabled = True
    Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Snare Drum"
    Else
        If Me.FirstDrumComboBox = "Rack Tom Drum" Then
        Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Rack.Enabled = True
        Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Bass/Kick Drun"
        Else
            If Me.FirstDrumComboBox = "Floor Tom Drum" Then
            Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Floor.Enabled = True
            Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Rack Tom"
            Else
                If Me.FirstDrumComboBox = "Bass Drum" Then
                Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!Bass.Enabled = True
                Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form!TypeOfDrum = "Floor Tom"
                End If
            End If
        End If
    End If

End Sub

to
Code:
Private Sub FirstDrumButton_Click()
    dim frm as access.form
    set frm = Forms!F_Contact_Details!F_Quote!SF_DrumOrderSpec.Form
    Me!SF_DrumOrderSpec.SetFocus
    Frm.ID_CUSTOMER = Me.ID_CUSTOMER
    Frm.ID_ORDER = Me.IDORDERPROCESS
    DoCmd.RunCommand acCmdSaveRecord
    Frm.Cyffredinol.Enabled = True
    Frm.Cyffredinol.SetFocus
    Forms!F_Contact_Details!F_Quote.Form!ArbedButton.Enabled = True
    select case me.firstDrumbox
      case "Snare Drum"
        Frm.Tannau.Enabled = True
        Frm.TypeOfDrum = "Snare Drum
      Case ""Rack Tom Drum"
        Frm.Rack.Enabled = True
        Frm.TypeOfDrum = "Bass/Kick Drum"
      Case "Floor Tom Drum"
        Frm.Floor.Enabled = True
        Frm.TypeOfDrum = "Rack Tom"
      Case "Bass Drum"
         Frm.Bass.Enabled = True
         Frm.TypeOfDrum = "Floor Tom"
     end select
End Sub

Easier to read and a lot easier to find errors.

Code:
Private Sub Priority_AfterUpdate()

    If Me.Priority = "1. Brys" Then
    Me.Priority.BackColor = vbRed
    Else
        If Me.Priority = "2. Uchel" Then
        Me.Priority.BackColor = RGB(255, 102, 0)
        Else
            If Me.Priority = "3. Canolig" Then
            Me.Priority.BackColor = vbYellow
            Else
                If Me.Priority = "4. Isel" Then
                Me.Priority.BackColor = vbGreen
                Else
                    If Me.Priority = "5. Dim" Then
                    Me.Priority.BackColor = vbWhite
                    End If
                End If
            End If
        End If
    End If

End Sub
to
Code:
Private Sub Priority_AfterUpdate()
    Select Case me.priority
      case "1. Brys"
        Me.Priority.BackColor = vbRed
      case "2. Uchel"
        Me.Priority.BackColor = RGB(255, 102, 0)
      case "3. Canolig"
        Me.Priority.BackColor = vbYellow
      case "4. Isel"
        Me.Priority.BackColor = vbGreen
      case "5. Dim"
        Me.Priority.BackColor = vbWhite
    end select
End Sub

Thanks for this - I've not experienced writing code in access like this before. Makes sense what you're saying.
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Did you even try what I posted? This is my image.
View attachment 80675


I have tried it and it's what is on there now. See image attached. - or maybe I am just not following what you're saying. I tried the text box after update, but I was getting this error 'runtime error 2465 application-defined or object-defined error'

I have also attached another 3 images of the exact problem. Maybe I am not explaining myself very well... But see attachments.

Phase 1 image is the listbox on the main menu form which lists all the Orders I then click on one of the 3 highlighted and it takes me to picture 2 (Phase 2). The other listbox then shows me the other 3 drums in that order which correlate back to the 3 records highlighted (phase 1) on the main menu listbox. The problem arises when I click on one of the next 2 records belonging to the order (phase 3). It brings up the same drum, I can't open the record on the other 2 drums, unless I open the form separately, but I don't really want to do that.

Again, thanks for your effort and time - it's well appreciated.
 

Attachments

  • Access linked forms.png
    Access linked forms.png
    91.9 KB · Views: 96
  • Phase 1.png
    Phase 1.png
    55.8 KB · Views: 95
  • Phase 2.png
    Phase 2.png
    108.1 KB · Views: 90
  • Phase 3.png
    Phase 3.png
    157.6 KB · Views: 105

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
Your tables make no sense to me. You have table CustomerOrder_Process which has a PK and a foreign key to a customer. That seems to make sense, a customer has an order. In that table order 11 belongs to customer 3.
In table CustomerOrder_Spec order 11 belongs to customer 1 and 3. What is the deal? Why is there a customer key in this table? That is part of the problem unless there is some reason, but makes no sense to me.



spec.jpg
 

monk_1987

Registered User.
Local time
Today, 23:03
Joined
Oct 5, 2012
Messages
31
Yeah, that's my bad. That shouldn't be there. I have deleted it now.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:03
Joined
May 21, 2018
Messages
8,463
Ok, that makes a big difference. I will send the fix in a while. I am out doing errands.
 

Users who are viewing this thread

Top Bottom