openrecordset script

[AndrewDotto]

Hi Guys,

I'm trying to adapt some code an ex-employee has written to perform a different function, not really too crash hot with the finer points of VBA unfortunatly. Anyone have any ideas?

Private Sub PropertyRef_AfterUpdate()
Dim rstTemp As Recordset
Dim qrydefTemp As QueryDef
Dim strSQL As String
Dim strRepairAgent As String

 
strSQL = "SELECT PRP_ADDRESS_1, PRP_ADDRESS_2, PRP_POST_CODE FROM xxxxxxx_WM_PROPERTY WHERE PRP_REFERENCE='" & [forms]![frmxxx]![propertyref] & "';"
Set rstTemp = CurrentDb.OpenRecordset(strSQL)
Address = rstTemp!PRP_ADDRESS_1 _
    & IIf(IsNull(rstTemp!PRP_ADDRESS_2), "", " " & rstTemp!PRP_ADDRESS_2)
PostCode = rstTemp!PRP_POST_CODE

Notes:
xxxxxxx_WM_PROPERTY is a linked table in the database.
[forms]![xxx]![propertyref] is a number field in the form.

I get a type mismatch error at the line strSQL... is it because i am trying to pass the form field into the string? Is there another way of doing this?


Thanks for your help,

Andrew

 

[vbaInet]

Most likely because PRP_REFERENCE is a Number data type. This is how you would set criteria to is:

... PRP_REFERENCE = " & [forms]![frmxxx]![propertyref] & ";"

Notice that I took out the single quotes. Single quotes for Text and hash (#) for Dates.

 

[Galaxiom]

I can't spot what would cause the error.

However you should change the way you refer to controls.
Address and Postcode should be Me.Address and Me.Postcode respectively.

Leaving out the reference to the current class (Me) leaves Access guessing. I have seen it go wrong.

The reference to PropertyRef via the Forms collection could also be be changed to Me.PropertyRef

 

[Galaxiom]

Notice that I took out the single quotes. Single quotes for Text and hash (#) for Dates.

That would not cause an error "at the line strSQL..."

However I expect that vbaInet is correct and you have reported the wrong line as the error.

It is important to provide the correct information.

 

[vbaInet]

That would not cause an error "at the line strSQL..."

It will at the OpenRecordset line. The OP obviously missed this because you will not get a type mismatch error on that line.

 

[vbaInet]

That would not cause an error "at the line strSQL..."

However I expect that vbaInet is correct and you have reported the wrong line as the error.

It is important to provide the correct information.

Ah... I didn't see your other statements replying.

 

[AndrewDotto]

Thanks for all this guys,

After changing the string to read as suggested, the error has now changed to read "Data type mismatch in criteria or expression"

When I get this running simple queries i understand it to mean i am filtering on a text string when i need a number or similar... is that the same idea here?

 

[vbaInet]

What line does it error?

What are you doing here?

Address = rstTemp!PRP_ADDRESS_1 _
    & IIf(IsNull(rstTemp!PRP_ADDRESS_2), "", " " & rstTemp!PRP_ADDRESS_2)

Why are you duplicating data? You don't need to save the appropriate address. Perform the calculation on-the-fly when you need it.

 

[AndrewDotto]

Righty -o guys... i've been a fiddlin....

This is how the SQL needs to read.

SELECT PRP_ADDRESS_1, PRP_ADDRESS_2, PRP_POST_CODE FROM TASKDBA_WM_PROPERTY WHERE PRP_REFERENCE="<PROPERTYREFNUMBERFROMFORM>";

to get the statement to work in an sql query, i needed to use the "".... but when i put this into the string, i cant get it to format correctly. How to you put a " into a string in VBA????

P.S. when i am assigning Address = blah blah.... i am assigning the value to a field on a form.....

 

[vbaInet]

My point is you shouldn't be saving that address because it already exists. Is this how the original developer did this?

 

[AndrewDotto]

By assigning it to the field Address, i am posting it to a local table, from a linked table within another database. I still need to know how to put a " in a vba script tho

 

[AndrewDotto]

Right, i've got it down to this, through Google and fiddling.

strSQL = "SELECT PRP_ADDRESS_1, PRP_ADDRESS_2, PRP_POST_CODE FROM TASKDBA_WM_PROPERTY WHERE PRP_REFERENCE=" & Chr(34) & [Forms]![frmemptyhomes]![PropertyRef] & Chr(34) & ";"
Set rstTemp = CurrentDb.OpenRecordset(strSQL)

If i comment out the set rsttemp and assign the output to be read, the resulting SQL statement works perfectly in a query, so why am i still getting a Runtime error "13", Type Mismatch on running it uncommented..... ARRRRRGH!!!

 

[vbaInet]

Is this text <PROPERTYREFNUMBERFROMFORM> enclosed in quotes in the table?

 

[AndrewDotto]

The SQL output needed to run is generated by the quoted code above vbaInet.... i dont know where i'm going wrong anymore

 

[vbaInet]

The question is, is the data saved with surrounding quotes or not. They are handled differently.

What is the data type of PRP_REFERENCE?

 

[AndrewDotto]

from what i can gather it is a PRP_REFERENCE is a text field. When i write the sql statement into a basic query i enter the reference using quotes "" to surround it.

 

[vbaInet]

Amend these and re-run your code:

Dim rstTemp As[B][COLOR=Red] DAO.[/COLOR][/B]Recordset
Dim qrydefTemp As [B][COLOR=Red]DAO.[/COLOR][/B]QueryDef

If it's still not working then you might want to re-check PRP_REFERENCE to ensure that it's a Text field.

Also, did you see GalaxiomAtHome's point about using the Me reference? Your code should read:

strSQL = "SELECT PRP_ADDRESS_1, PRP_ADDRESS_2, PRP_POST_CODE FROM TASKDBA_WM_PROPERTY WHERE PRP_REFERENCE = " & Chr(34) & [B][COLOR=Red]Me[/COLOR][/B]!PropertyRef & Chr(34) & ";"

 

[Galaxiom]

Set a break point before the CreateRecordset line Have a look at strSQL in the Immediate or Locals window.

 

[AndrewDotto]

Thanks for all your help guys, i've just rewritten the guys code and now it's working, thanks for all your help

 

[vbaInet]

That's good to hear.

Glad we could help!