CODEWITHSUNDEEP
ms-access
Baper
Baper

Reputation: 1543

Runtime query does not work.Why?

I have a Problem with my codes and it made me confuse.this is my scenario:

in my access Project I have a Form and subform that work together.I wanted to enhace my performance . I deceided to load my form and subforms in Runtime(from this article) .I put my query on my form code, in Form_load event like this:

Private Sub Form_load()
    Me.RecordSource = "SELECT DISTINCTROW tb_bauteile.* " & _
   "FROM tb_bauteile LEFT JOIN FehlerCodes_akt_Liste ON tb_bauteile.CDT = FehlerCodes_akt_Liste.CDT " & _
   "WHERE (((FehlerCodes_akt_Liste.Steuergerät)='MEDC17'))" & _
   "ORDER BY FehlerCodes_akt_Liste.Fehlerpfad;"
End Sub

but another problem occured with another controls in my form.when i click another control .this function should be run:

Private Sub subPfadFilter(Kombifeld As Variant, Obd As String)
    Dim strKrit, strAuswahl, strSg As String

     If (IsNull(Me.CDT) Or (Me.CDT = "")) Then
            strAuswahl = "*"
        Else
            strAuswahl = Me.CDT
            blnFiltOn = True
        End If
    .....

but it doesn't work and say me method or dataobjectt not found( without this if statement works the function works fine .the problem come from CDT ) if I put the query in Datasource in my form property that works properly

I cant understand the relation between these two things(putting the query on datasource of form property then working the if statement (CDT) good but when i put the query in Form load that does not work ) ,would you please help me if posible?have you any idee? thank you so much for your helps

Upvotes: 0

Views: 362

Answers (1)

Matt Donnan
Matt Donnan

Reputation: 4993

When using the "Me" reference, you need to ensure the code is behind the present form, you've mentioned that the Form_Load code is on there, but is the "subPfadFilter" somewhere else?

If so then it needs to either be moved on to this form, or change your reference to the forms name instead of "Me".

If it is on the form then the error seems to be complaining about the CDT item, you will need to confirm that this is the name of either a text field or a control on this form.

If it is then you can try inserting:

Me.Requery

After your SQL statement, this may wake up the forms reference to the object.

This bit is not essential but to avoid carrying out two tests on CDT and streamlining your code slightly I would amend to this:

If Me.CDT & "" = "" Then
    strAuswahl = "*"
Else
    strAuswahl = Me.CDT
    blnFiltOn = True
End If

This captures both Is Null or "" in one shot.

UPDATE:

For an unbound form, you need to set your form's controls via VB as well as the forms recordsource e.g:

Private Sub Form_load()

Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCTROW b.* " & _
                                 "FROM tb_bauteile As b LEFT JOIN FehlerCodes_akt_Liste As f " & _
                                 "ON b.CDT = f.CDT " & _
                                 "WHERE f.Steuergerät = 'MEDC17' " & _
                                 "ORDER BY f.Fehlerpfad;")

    rs.MoveFirst

    Me.CDT = rs!CDT

    'Any other fields go here

    rs.Close
    Set rs = Nothing

End Sub

However if you are returning more than one record at a time then you will either need to stick with a bound form or perhaps use a listbox to display your information. Unbound forms do not use the Forms "Recordset" property.

Note: I have also 'Aliased' the table names in your SQL statement, this is a good practice to get into : )

Upvotes: 3

Related Questions