Opening a recordset

[GaelicFatboy]

Ladies & Gents,

Once again I'm here knowing exactly how I need to structure my software to run, but I'm at a complete loss as to what the correct syntax should be.

I've got a query called My_Query with three fields namely PartNumber, AccountNumber and Discount. I've got some code to look at this query and update the Discount field for a given account number, but I am unable to code the filter on the query with regards to the account number. My code is as follows:

Dim My_DataBase as DAO.Database
Dim My_RecordSet as DAO.RecordSet
Dim My_QueryDef as QueryDef

Set My_DataBase = CurrentDb
Set My_QueryDef = My_DataBase.QueryDefs("My_Query")
My_QueryDef.Parameters("AccountNumber") = 777 'this is the line that causes the error
Set My_RedordSet = My_DataBase.OpenRecordSet("My_Query",dbOpenDynaset)
My_RecordSet.FindFirst "PartNumber=""" & Me.Text_PartNumber & """"
If Not .NoMatch Then
.Edit
![Discount] = Me.Text_Discount
.Update
.Close
End If
End With

Question do I need to Dim any more variables?

Cheers

D

 

[Mile-O]

'this is the line that causes the error

Er, what is the error?

But it may be that the field AccountNumber is a Text field, in which case you need to make the 777 value a string literal, i.e. ("777").

 

[GaelicFatboy]

The error is:

3265: Item not found in collection

AccountNumber is a long integer

Cheers

D

 

[Mile-O]

Have you set up a Parameter called AccountNumber in the Query Editor? From the Query->Paramters menu.

 

[GaelicFatboy]

The query pulls all three fields from a table and I can view the query when operating the form to which it is associated. Also the query is filtering the records correctly to give me four results, but I just can't get the code to run to edit the discount value from the query.

D

 

[Mile-O]

Is the query updateable?

 

[GaelicFatboy]

Yes it is.

D

 

[Mile-O]

Can you post the query SQL, please?

 

[GaelicFatboy]

Here you go.

SELECT Table_SpecialDiscount.PartNumber, Table_SpecialDiscount.SpecialDiscount, Table_SpecialDiscount.AccountNumber
FROM Table_SpecialDiscount
WHERE (((Table_SpecialDiscount.AccountNumber)=[Forms]![PriceListCompiler_Form]![Combo_Customer]));


D

 

[GaelicFatboy]

Openning a recordset

I'm testing out trying to open a query recordset on a query called "Discount_Query" but keep getting the following error:

3061: Too few parameters. Expected 1.

I've got a single query looking at a single table, there are three fields in the query and I'm filtering the data around the account number to edit the discount.

Query SQL:
PARAMETERS AccNumber Long;
SELECT Discount_Table.AccountNumber, Discount_Table.PartNumber, Discount_Table.Discount
FROM Discount_Table
WHERE (((Discount_Table.AccountNumber)=[AccNumber]));


Code:
Dim My_RecordSet As DAO.Recordset, My_DataBase As DAO.Database
Dim My_QueryDef As QueryDef

Set My_DataBase = CurrentDb
Set My_QueryDef = My_DataBase.QueryDefs("Discount_Query")
My_QueryDef.Parameters("AccNumber") = Me.Text_AccNumber
Set My_RecordSet = My_DataBase.OpenRecordset("Discount_Query") 'the error occures here
With My_RecordSet
.FindFirst "PartNumber=" & Me.Text_PartNumber
If Not .NoMatch Then
.Edit
![Discount] = Me.Text_Discount
.Update
.Close
Else
MsgBox "Part number not found, please try again.", vbOKOnly, "Update Error..."
End If
End With


Would anyone be able to shed some light on how to edit data in a query using VB.

Cheers one and all.

D

 

[KeithG]

try using the Openrecordset method of the querydef.

 

[pbaldy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[Mile-O]

Can I take it by this thread that you solved it, since you now have a new error?

 

[Brianwarnock]

Lack of feedback is prevalent on the forum these days.

Brian

 

[GaelicFatboy]

Sorry for the delay gents I've been away from my computer for a few hours with a customer.

I managed to get passed the last error by adjusting this line:

My_RecordSet.FindFirst "PartNumber=""" & Me.Text_PartNumber & """"

To this:
My_RecordSet.FindFirst "PartNumber=" & Me.Text_PartNumber

But it just moves the error trap down to the openrecordset line. I've got round the problem by defining a new query, including the filter within the SQL text, then running the edit and finally deleting the query. However, this all seems a little long winded.

I just can't get the openrecordset part to run, it keep coming up with an error saying expected parameters, hence the new tread.

Any ideas as to how to get this query to open using openrecordset?

Cheers for your help anyway.

D

 

[GaelicFatboy]

Got it working.

Mile-O I guess you joined my two threads together but you've dropped the reply that solved the problem for me. One of my replys had a link to a web site:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

The line causing all the problems is this one:

My_QueryDef.Parameters("AccNumber") = Me.Text_AccNumber

It's the ".Parameters" part that's doing it. I've rejigged a few things with the query, but the following syntax cracks it.

My_AccNum = Me.Text_AccountNumber
My_QueryDef![Forms!Discount!Text_AccountNumber] = My_AccNum

And there you have it.

Cheers for all your help everyone, Mile-O if you let me know the name of the punter who dropped me the link above I'll add to his rep list.

Once again cheers.

D

 

[Mile-O]

Ah, okay. No idea how the other thing got lost. But I was just going to suggest this since you were doing all the work to build a QueryDef and then opening a Recordset based on the same query. However, the QueryDef wasn't assigned to the Recordset object.

Hence, why you need the line in bold:
It's been so long since I used QueryDefs, but

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

Set db = CurrentDB
Set qd = db.QueryDefs("Discount_Query")

qd.Parameters("AccNumber") = Me.Text_AccNumber

[b]Set rs = qd.OpenRecordSet[/b]

With rs
    .FindFirst "PartNumber = " & Me.Text_PartNumber
    If Not .NoMatch Then 
        .Edit
        ![Discount] = Me.Text_Discount
        .Update
        .Close
    Else
        MsgBox "Part number not found, please try again.", vbOKOnly, "Update Error..."
    End If
End With

Set rs = Nothing
set qd = Nothing
Set db = Nothing

 

[Guus2005]

Type the following in the immediate window to get the number of parameters in a query:

?currentdb.QueryDefs("Discount_Query").parameter.count

To get their names:

?currentdb.QueryDefs("Discount_Query").parameter(0).name
?currentdb.QueryDefs("Discount_Query").parameter(1).name
...

When you see a parameter which you didn't define on purpose, that's where you have to look.

Enjoy!

 

[GaelicFatboy]

Cheers Guus I used those from your previous post to sort out the problem, and they came in well hand.

D

 

[kiashi]

Hello, I have just dropped into this thread while doing searches based on a similar problem I am having with Access 2003.

My VBA is extremely rusty but I figure this shouldn't be too difficult. Please see the red section in the quoted code below explaining how that particular line does not work for me. When I use this I get an error stating "Too few parameters, expected 1". it seems to be looking for the optional, type, options and lockedit parameters?

All I am trying to do is open a recordset based on a query that I have already created in design view. If I inject the SQL statement directly into the code it works like:

    Set db = CurrentDb
    Set rec = db.OpenRecordset("SELECT tblCase.Case_Num FROM tblCase ORDER BY tblCase.Case_Num")    
    rec.MoveLast
    intJobNum = rec("Case_Num")
    intJobNum = intJobNum + 1
    rec.Close
    txtCaseNum.Value = intJobNum

Is there something I am missing here?

Ah, okay. No idea how the other thing got lost. But I was just going to suggest this since you were doing all the work to build a QueryDef and then opening a Recordset based on the same query. However, the QueryDef wasn't assigned to the Recordset object.

Hence, why you need the line in bold:
It's been so long since I used QueryDefs, but

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

Set db = CurrentDB
Set qd = db.QueryDefs("Discount_Query")

qd.Parameters("AccNumber") = Me.Text_AccNumber

[b]Set rs = qd.OpenRecordSet[/b] '[COLOR="Red"]<--This does not work for me in Access 2003[/COLOR]

With rs
    .FindFirst "PartNumber = " & Me.Text_PartNumber
    If Not .NoMatch Then 
        .Edit
        ![Discount] = Me.Text_Discount
        .Update
        .Close
    Else
        MsgBox "Part number not found, please try again.", vbOKOnly, "Update Error..."
    End If
End With

Set rs = Nothing
set qd = Nothing
Set db = Nothing