Query from form to subform

[Drunkenneo]

I have searched all over but not got any idea, Situation is i have a subform which is controlled by a query eg . Fname&" "& Lname as Name, order_number. and i have form which have combobox.

When form opens the query works fine but when i search value order_number in combo box it gives the result but with ?Name in Name column.

I know i can do this with Filter method, and rewriting the query, but i want to use it by passing the value in query of subform. Please Help.

 

[pr2-eugin]

What is the CODE behind the ComboBox?

 

[Drunkenneo]

Private Sub ord_id_AfterUpdate()
'DISPLAY RECORDS FROM MASTER ON BASIS OF COMBOBOX VALUES




On Error GoTo Exit_cmd_Details_exp_Click
Forms!Master_Table!fsub_master.Form.RecordSource = "select * from Master_table where norder_number =" & ord_id.Value & ""
'DISPLAYING RECORDCOUNT AT BOTTOM OF PAGE


On Error GoTo Exit_cmd_Details_exp_Click
If (Forms!Master_Table!fsub_master.Form.RecordsetClone.RecordCount <> 0) Then
With Forms!Master_Table!fsub_master.Form.RecordsetClone
.MoveLast
Tcount1.Caption = .RecordCount
End With
Else
MsgBox "Invalid Order Number"
Tcount1.Caption = 0
End If

Exit_cmd_Details_exp_Click:
Exit Sub

Err_cmd_Details_exp_Click:

MsgBox Err.Description

Resume Exit_cmd_Details_exp_Click
End Sub



And code behind the subform:

SELECT Master_Table.Master_ID, strConv(Master_Table.contact_first_name, 3)&" " & strconv(Master_Table.contact_last_name,3) AS Name, Master_Table.oorder_number, Master_Table.norder_number, Master_Table.order_date, Master_Table.quantity, Master_Table.Ecard, Master_Table.Ereference, Master_Table.Tracking_No, Master_Table.Date_Dispatched, Master_Table.EmailSent_Date, Master_Table.EmailResponse_Date, Master_Table.Payment_Mail_Date, Master_Table.Paid_Date, Master_Table.ActivationEmailSent_date, Master_Table.ActivationEmailRecieved_Date, Master_Table.Cards_Activated_Date, Master_Table.Cust_Status, Master_Table.Invoice_Sent_Date
FROM Master_Table;

 

[pr2-eugin]

You have a Calculated Column in the SubForm's Recordsource "Name", when using the * FROM Master_Table1, the Calculated Column could not be found, so the compiler does not know what field to replace that.. So the Error.. Try the following CODE, it should sort out the error..

Private Sub ord_id_AfterUpdate()
On Error GoTo Err_cmd_Details_exp_Click
   [COLOR=Green] 'DISPLAY RECORDS FROM MASTER ON BASIS OF COMBOBOX VALUES[/COLOR]
    Forms!Master_Table!fsub_master.Form.RecordSource = "SELECT StrConv(Master_Table.contact_first_name, 3) & " " & _
                                                       "StrConv(Master_Table.contact_last_name,3) AS [Name], Master_Tablel.* " & _
                                                       "FROM Master_table WHERE norder_number = " & ord_id.Value
    
   [COLOR=Green] 'DISPLAYING RECORDCOUNT AT BOTTOM OF PAGE[/COLOR]
    If (Forms!Master_Table!fsub_master.Form.RecordsetClone.RecordCount <> 0) Then
        With Forms!Master_Table!fsub_master.Form.RecordsetClone
            .MoveLast
            Tcount1.Caption = .RecordCount
        End With
    Else
        MsgBox "Invalid Order Number"
        Tcount1.Caption = 0
    End If

Exit_cmd_Details_exp_Click:
    Exit Sub

Err_cmd_Details_exp_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Details_exp_Click
End Sub

Although, I would point out Name is a very bad Field/Control/Object name.. As Name is a reserved word in Access and would lead into complication.. Try renaming it to something like "fullName", "customerName", "clientName" etc..

A list of all Reserved Keywords : http://support.microsoft.com/kb/286335

A good Naming convention document : http://www.access-programmers.co.uk/forums/showthread.php?t=225837