Hi all,
please help me with my problem.
i'm using a parameter query wretten in VB as in the attachement, this is done in Microsoft access but my problem is i don't know how to link the resultd data from the query with a form in Microsoft access.

Thanx is advance...
Seem


----------------------------------------------------------------------
Private Sub Combo41_AfterUpdate()
Dim cmd1 As Command
Dim rs1 As Recordset, str1 As String
Dim fldLoop As ADODB.Field
Dim prm1 As ADODB.Parameter, int1 As Integer
Dim frm1 As Form
Dim i As Integer

'Form_PurchaseOrder.RecordSource = "TOP"

Set cmd1 = New ADODB.Command

With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "Parameters [POnum] Long;" & _
                "SELECT PurchaseOrder.Description, PurchaseOrder.COdate, " & _
                "PurchaseOrderDetails.totalPrice, [value1]*[totalPrice]/100 AS v1, " & _
                "[value2]*[totalPrice]/100 AS v2, [value3]*[totalPrice]/100 AS v3, " & _
                "[value4]*[totalPrice]/100 AS v4, [value5]*[totalPrice]/100 AS v5, " & _
                "PurchaseOrder.POstatus, PurchaseOrder.POnum, TermsOfPayment.TermsOfPay " & _
                "FROM (PurchaseOrder INNER JOIN PurchaseOrderDetails ON " & _
                "PurchaseOrder.POnum = PurchaseOrderDetails.POnum) INNER JOIN " & _
                "TermsOfPayment ON PurchaseOrder.termsOfPay = TermsOfPayment.TermsOfPay " & _
                "WHERE (((PurchaseOrder.POnum) = [POnum]))"
    .CommandType = adCmdText
End With

Set prm1 = cmd1.CreateParameter("[POnum]", adInteger, adParamInput)
cmd1.Parameters.Append prm1
prm1.Value = Form_PurchaseOrder.POnum.Value

cmd1.Execute

end sub

-----------------------------------------------------------------------

____________________________
Seem Haroun

Hi,
Easy job!! You have already declared a Recordset Object (rs1) so use this. Once you have opened it (by setting it to your Command Object cmd1) then just set the Form.RecordSource Property to the Recordset.

'In place of cmd1.Execute type Set rs1 =  cmd1.Execute Form.RecordSource = rs1

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

thanx really for replying so soon.
But when i tried the solution u gave me there was the following compile error: type mismatch ...

so please i need ur help.   

____________________________
Seem Haroun

Hi,
Should that be Set form.recordsource? BTW I didn't know you could do that in access.. thought it was something to do with recordsetclone.
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hi,
Well according to the MS Access Help Files you can set the Forms Recordset Property to an ADODB.Recordset Object. I have never actually tried it before, usually either binding direct to a table or a query, or working with a disconnected recordset in code. Seem is quite right though, when you try to do it Access throws its dummy out of the pram! So, the only other way is to set the Record Source property directly to the constructed SQL String, as in :-

Dim strSQL strSQL = "SELECT ...... etc." Form.RecordSource = strSQL

I KNOW that works !



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi,
Thanx really for the help, i appreciate it.
i tried the solution GeoffS gave me, and it works. BUT the problem is, it does not solve the problem that made me go to the VB in the first place, and it is the parameter query. coz i needed a parameter query but i wonted the parameter to take its value outomatically from a combobox in the form. and not to inter its value manually.
so if u can help me in this i'll be thankful.

Thanx for the help

____________________________
Seem Haroun

Hi,
Well presumably your users will be selecting from the ComboBox and you want to get the Recordset based upon their selection. So just assign the value of their selection to a Variable and re-run the SQL.

Private Sub ComboBox1_Click() Dim strSQL As String Dim lngPOnum As Long lngPOnum = ComboBox1.Column(0) strSQL = "SELECT ...... etc." _ &  "WHERE (((PurchaseOrder.POnum) = " & lngPOnum & "))" Form.RecordSource = strSQL End Sub

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

hi its me again...
thank u really for ur help.
i tried the strSQL solution but nothing apeared in the form am i doing something wrong? or do i need to do something i'm not aware of?
thanx again

____________________________
Seem Haroun

Hi,
If nothing appeared then that would be because your SQL Query did not return any records. This could be due to the query not being constructed properly (although this should throw an Error if there is a syntax problem) or because the POnumber does not exist. Without seeing a bit more of your code it is a bit difficult to be more precise. If you can you post the code I will try to see what your problem is.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

hi,
i just wonder the advantages of using SQL instead of developing the database using the wizard in access.