Object Required Error Checking for Empty Recordset (1 Viewer)

[AJR]

Hi

I am soooooo close to getting this data entry form complete. The Database keeps track of transactions in bank accounts with differing currencies. The exchange rate between currencies is recorded in tblFXRates and I have to check, before adding a record to tblTransactions, using the data entry form ppuNewCashTransaction, that an exchange rate has been entered for the Date and Currency of the transaction. There's lots of examples of this on the internet but I can't get any to work.

Here's the code:

'Check if Query for FX is empty  i.e.,No exchange rate in table for this currency on this date
 
Dim rst As Recordset
 
 Set rst = db.OpenRecordset("SELECT tblFXRates.fxXRate " & vbCrLf & _
 "FROM tblFXRates " & vbCrLf & _
 "WHERE (((tblFXRates.fxDate)=[forms]![ppuNewCashTransaction]![txtTransDate]) AND ((tblFXRates.fxCurrency)=[forms]![ppuNewCashTransaction]![txtFXShortName]));")
 
 If rst.EOF Then MsgBox "No FX Rate Exists"

 Exit Sub

I am entering this in the on_click event of a Command Button Control on the data entry form, right after I check to ensure that all the fields in the form have had values entered in them.

I get an Object Required error, on the line starting with "Set". Both qualifiers return values when I enter them in the Immediate Window and the spelling is correct for the table name and field being selected.

Any help would be appreciated, as always. Sooooooo close.

Thanks

A/R

 

[CJ_London]

the sql to your openrecordset is invalid - rather than set the code directly, assign it to a string and debug.print it to see what it looks like.

dim sqlstr as string

sqlstr="SELECT tblFXRates.fxXRate " & vbCrLf & _
"FROM tblFXRates " & vbCrLf & _
"WHERE (((tblFXRates.fxDate)=[forms]![ppuNewCashTransaction]![txtTransDate]) AND ((tblFXRates.fxCurrency)=[forms]![ppuNewCashTransaction]![txtFXShortName]));"
debug.print sqlstr
Set rst = db.OpenRecordset(sqlstr)

Note there there is no need for the vbcrlf

Openrecordset is not the same as running sql (e.g. docmd.openquery) - so you do not refer to forms in the way you have - when building the sqlstr, you need to provide the values - not somewhere the code can get the values from. You also need to use the # for dates (and use US formatting) and ' for text

Assuming you are running this code from your ppuNewCashTransaction form your code would be

sqlstr="SELECT fxXRate FROM tblFXRates " & _
"WHERE fxDate=#" & format([txtTransDate],"mm/dd/yyyy") & "#  AND fxCurrency ='" & [txtFXShortName] & "'"

 

[JHB]

You can't refer to a form object in a recordset, you need the value instead.
Wrong:

"WHERE (((tblFXRates.fxDate)=[forms]![ppuNewCashTransaction]![txtTransDate]) 
AND ((tblFXRates.fxCurrency)=[forms]![ppuNewCashTransaction]![txtFXShortName]));")

Try instead:

"WHERE (((tblFXRates.fxDate)=#" & [forms]![ppuNewCashTransaction]![txtTransDate] & "#) 
AND ((tblFXRates.fxCurrency)='" & [forms]![ppuNewCashTransaction]![txtFXShortName])) & "';")

 

[AJR]

CJ_London and JHB

Thanks. I think I actually get all that. I was using a form I made, from instructions by allen browne, that translates SQL to VBA and then trying to use it in SQL--Duuhh. That's also why the Carriage Return stuff is there.

I'll get at this right away. This form's module is the first time I've used RecordSets so I'm a little confused. I guess that's pretty obvious.

Thanks Again

A/R

 

[AJR]

Hi

I tried the following but am still getting the "Object Required" error on the line:

Set rst = db.OpenRecordset(sqlstr)

'Check if Query for FX is empty  i.e.,No exchange rate in table for this currency on this date
 Dim rst As Recordset
 Dim sqlstr As String
 sqlstr = "SELECT fxXRate FROM tblFXRates " & _
          "WHERE fxDate=#" & Format([txtTransDate], "mm/dd/yyyy") & "#  AND fxCurrency ='" & [txtFXShortName] & "'"
Set rst = db.OpenRecordset(sqlstr)
If rst.EOF Then MsgBox "No FX Rate Exists"
Exit Sub

Am I doing this as suggested?

Thanks

A/R

 

[AJR]

Hi

Still haven't gotten it sorted.

When I run this with debug.print I get:

SELECT fxXRate FROM tblFXRates WHERE fxDate=#02-17-2016# AND fxCurrency ='CAD'

If I paste that into the query Grid as SQL it works fine. So, I'm really not understanding why I am getting the Object Required error.

???????

A/R

 

[arnelgp]

you should Declare variable db:

Dim db As Dao.Database

set db = Currentdb

 

[AJR]

Hi Arnegp

Thanks for the suggestion. I tried that and now I get a different error.

"Too Few Parameters expected 1"

A/R

 

[arnelgp]

try qualifying your controlname with a form name, ie:

[forms]![ppuNewCashTransaction]![txtTransDate]

or if the code is in the same form:

Me![txtTransDate]

also check the fieldnames on your query against the fieldname on yourtable if they are the same. check the controlnames also.

 

[AJR]

O.K.

Got it. After getting the "Too Few Parameters" error I tried copying the SQL generated by the Debug.Print statement back into the Query Grid as SQL. That showed the problem to be the reference to the Selected field, which I had referenced as tblFXRates.fxXrate. The spelling of everything was correct so I removed the tblFXRates part and used simply [fxXrate].

That did the trick.

Here's the final code:

'Check if Query for FX is empty  i.e.,No exchange rate in table for this 'currency on this date
    Dim rst As Recordset
    Dim sqlstr As String
    Dim db As DAO.Database
    sqlstr = "SELECT [fxXRate] FROM tblFXRates " & _
             "WHERE fxDate= #" & txtTransDate & "#  AND fxCurrency ='" & txtFXShortName & "'"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(sqlstr)
        If rst.EOF Then
            MsgBox "No FX Rate Exists"
            Exit Sub
        End If

Thanks So Much to all assisted. I really learned a lot on this one.

A/R