Strange Issue

[timorous]

Hello

I have a database with two main forms built on the same template. The main form is set up in 3 sections, 1. A subform which contains a list of selectable items, 2. Search criteria which will filter the subform list and 3. A detail subform which shows the information for the item selected in the list subform.

I am having an issue where the detail_subform.recordsource property is not updating the displayed information. I can set the recordsource in code and view the correct text box values that relate to that query but when the execution ends the subform is showing that results from the record source as set in the properties window.

What is puzzling is that It works fine on one form but not the other and if I load the non working form then go into the code window and press reset it starts working fine.

The code for the working form is as follows.

Private Sub Form_Current()

    Employee_Method.get_listbox_data

End Sub

Public Sub get_listbox_data()

    Dim SQLCONTACT As String
    Dim SQLGEN As String
    Dim SQLLIC As String
    Dim SQLNOK As String
    Dim SQLTRAVEL As String
    
    On Error Resume Next
    
    If IsNull(Form_employee_list_sub.employee_id_box) Then
        'do nothing
    Else
        SQLCONTACT = "SELECT street, district, town, county, postcode, landline, mobile, email " & _
                     "FROM dbo.employees " & _
                     "WHERE dbo.employees.employee_id = " & Form_employee_list_sub.employee_id_box
        
        SQLGEN = "SELECT first_name, last_name, date_of_birth, employed, staff_type, daily_wage " & _
                 "FROM dbo.employees " & _
                 "WHERE dbo.employees.employee_id = " & Form_employee_list_sub.employee_id_box

        SQLLIC = "SELECT car_licence, car_licence_expire, car_photo_expire, digi_card, digi_card_expire, hgv_licence, hgv_licence_expire, " & _
                 "hgv_photo_expire, international_licence, international_expire " & _
                 "FROM dbo.employees " & _
                 "WHERE dbo.employees.employee_id = " & Form_employee_list_sub.employee_id_box

        SQLNOK = "SELECT next_of_kin, next_of_kin_landline, next_of_kin_mobile, next_of_kin_email " & _
                 "FROM dbo.employees " & _
                 "WHERE dbo.employees.employee_id = " & Form_employee_list_sub.employee_id_box

        SQLTRAVEL = "SELECT passport, passport_expire, passport_issued, passport2, passport2_expire, passport2_issued " & _
                    "FROM dbo.employees " & _
                    "WHERE dbo.employees.employee_id = " & Form_employee_list_sub.employee_id_box
    End If
    
    Form_employee_bank_sub.RecordSource = SQLBANK
    Form_employee_contact_sub.RecordSource = SQLCONTACT
    Form_employee_gen_sub.RecordSource = SQLGEN
    Form_employee_lic_sub.RecordSource = SQLLIC
    Form_employee_nok_sub.RecordSource = SQLNOK
    Form_employee_travel_sub.RecordSource = SQLTRAVEL
    
    datecompare = Format(DateAdd("m", 1, Date), "yyyy-mm-dd")
    
    If Form_employee_lic_sub.car_license_expire < Format(Date, "yyyy-mm-dd") Or Form_employee_lic_sub.car_photo_expire < Format(Date, "yyyy-mm-dd") Or _
       Form_employee_lic_sub.hgv_license_expire < Format(Date, "yyyy-mm-dd") Or Form_employee_lic_sub.hgv_photo_expire < Format(Date, "yyyy-mm-dd") Or _
       Form_employee_lic_sub.digi_card_expire < Format(Date, "yyyy-mm-dd") Or Form_employee_lic_sub.international_expire < Format(Date, "yyyy-mm-dd") Then
        
        Form_employee_main.tab4_label.BackColor = RGB(255, 0, 0)
        Form_employee_main.tab4_label.ForeColor = RGB(255, 255, 255)
    
    ElseIf Form_employee_lic_sub.car_license_expire < datecompare Or Form_employee_lic_sub.car_photo_expire < datecompare Or _
       Form_employee_lic_sub.hgv_license_expire < datecompare Or Form_employee_lic_sub.hgv_photo_expire < datecompare Or _
       Form_employee_lic_sub.digi_card_expire < datecompare Or Form_employee_lic_sub.international_expire < datecompare Then
       
        Form_employee_main.tab4_label.BackColor = RGB(255, 204, 153)
        Form_employee_main.tab4_label.ForeColor = RGB(0, 0, 0)
    Else
        Form_employee_main.tab4_label.BackColor = RGB(255, 255, 255)
        Form_employee_main.tab4_label.ForeColor = RGB(0, 0, 0)
    End If
    
    If Form_employee_travel_sub.passport_expire < Format(Date, "yyyy-mm-dd") Or Form_employee_travel_sub.passport2_expire < Format(Date, "yyyy-mm-dd") Then
        Form_employee_main.tab5_label.BackColor = RGB(255, 0, 0)
        Form_employee_main.tab5_label.ForeColor = RGB(255, 255, 255)
    ElseIf Form_employee_travel_sub.passport_expire < datecompare Or Form_employee_travel_sub.passport2_expire < datecompare Then
        Form_employee_main.tab5_label.BackColor = RGB(255, 204, 153)
        Form_employee_main.tab5_label.ForeColor = RGB(0, 0, 0)
    Else
        Form_employee_main.tab5_label.BackColor = RGB(255, 255, 255)
        Form_employee_main.tab5_label.ForeColor = RGB(0, 0, 0)
    End If    
       
End Sub

and this is the code for the non working form

Private Sub Form_Current()

    Jobs_Method.get_selected_job_data

End Sub

Public Sub get_selected_job_data()

    Dim SQLGEN As String
    Dim SQLDET As String
    
    On Error Resume Next
    
    If IsNull(Form_jobs_list_sub.job_number_text) Then
        'do nothing
    Else
        SQLGEN = "SELECT job_number, booked, order_date, client_name " & _
                 "FROM dbo.jobs " & _
                 "INNER JOIN dbo.clients " & _
                 "ON dbo.jobs.client_id = dbo.clients.client_id " & _
                 "WHERE dbo.jobs.job_number = " & Form_jobs_list_sub.job_number_text
        
        SQLDET = "SELECT job_number, job_description, from_date, to_date, from_location, to_location, notes " & _
                 "FROM dbo.jobs " & _
                 "WHERE dbo.jobs.job_number = " & Form_jobs_list_sub.job_number_text        
    End If
    
    Form_jobs_general_sub.RecordSource = SQLGEN
    Form_jobs_detail_sub.RecordSource = SQLDET
    
End Sub

The main difference between working and non working is that the working one is also doing some formatting on the main form but I can comment this block out and it still works fine.

I have compared the properties between all the subforms and I cannot seem to find anything that is different between the working and non working forms.

This one really has me stumped so any help or suggestions would be great.

Cheers

 

[timorous]

I have fixed this issue.

It is the order in which the sub-forms are created on the main form that matters. If I create the list subform first then the detail subforms it does not work correctly but if I create the detail subforms first then the list subform it does work.

As there was nothing wrong with the underlying code I guess this should be moved into the Forms subforum. I would also appreciate it if the thread title could be changed to something a bit more descriptive like, "Issue with subforms recordsource and creation order.", that would be fantastic.