Form data in SELECT statement recordset

[nvrwrkn]

Getting the Expected 3 runtime error from the following and I believe it has to do with how I'm using the form data, I've read that form data isn't available to recordset.

Public Function GetLoc()
Dim GeoData As DAO.Database
Dim Location As DAO.Recordset
Set GeoData = CurrentDb
Set Location = GeoData.OpenRecordset("SELECT TOP 1 CStr(CInt(GeoData!Area))+GeoData!Block+GeoData!Space " & _
        "FROM GeoData " & _
        "WHERE (((GeoData.EVEN_ODD_I)=IIf(Right([Forms]![Activity]![StreetNo],1)=0,'E',IIf(Right([Forms]![Activity]![StreetNo],1)=2,'E',IIf(Right([Forms]![Activity]![StreetNo],1)=4,'E',IIf(Right([Forms]![Activity]![StreetNo],1)=6,'E',IIf(Right([Forms]![Activity]![StreetNo],1)=8,'E','O')))))) And ((GeoData!STREET_DIRECTION_CD)=[Forms]![Activity]![StreetDir]) And ((GeoData!STREET_NME) Like [Forms]![Activity]![StreetName]+'*') And (([Forms]![Activity]![StreetNo]) Between GeoData!LOW_ADDRESS_NO And GeoData!HIGH_ADDRESS_NO)) ")
   
Debug.Print Location

        
End Function

GeoData is a linked table. Form is open and data is inside references, above works in a query, I copied it directly from the query.



I read something about putting the form references outside quotes but I'm not seeing how to do that, tried it a few ways and nothing worked.

[pbaldy]

Well, this type of thing:


"...IIf(Right(" & [Forms]![Activity]![StreetNo] & ",1)=0..."

But you don't need all that to determine even/odd, you can do it with the Mod function:

"Any Even Number" Mod 2 = 0
"Any Odd Number" Mod 2 = 1

[nvrwrkn]

thanks for the MOD reminder ;/ always forget that one.

Now I'm getting method of object forms error.

Public Function GetLoc()
Dim GeoData As DAO.Database
Dim Location As DAO.Recordset
Set GeoData = CurrentDb
Set Location = GeoData.OpenRecordset("SELECT TOP 1 CStr(CInt(GeoData!Area))+GeoData!Block+GeoData!Space " & _
        "FROM GeoData " & _
        "WHERE (((GeoData.EVEN_ODD_I)=IIf(" &  [Forms]![Activity]![StreetNo] & " MOD 2 = 0,'E','O')) And  ((GeoData!STREET_DIRECTION_CD)=" & [Forms]![Activity]![StreetNo]  & ") And ((GeoData!STREET_NME) Like " &  [Forms]![Activity]![StreetNo] & "+'*') And ((" &  [Forms]![Activity]![StreetNo] & ") Between GeoData!LOW_ADDRESS_NO  And GeoData!HIGH_ADDRESS_NO)) ")
   Debug.Print Location

Form is open, correct data available.

[pbaldy]

Use a variable for the SQL and this technique will make it easier to find the problem:

http://www.baldyweb.com/ImmediateWindow.htm

Is StreetNo a text field in the table?

[nvrwrkn]

I'll give that a try, I used the debug recommendation from that link earlier.

StreetNo is a text field on the form, it's used between the two table fields HIGH_ADDRESS & LOW_ADDRESS which are number fields. The table is a GIS table where areas are mapped to specific numbered boundaries. One side of the street could be one boundary number, the other side of the street could be another, hence the even odd comparison.

[nvrwrkn]

won't output the string, same error regarding the form item.

[pbaldy]

A text field requires delimiters in the WHERE clause, like the apostrophes in red:


"...((GeoData!STREET_DIRECTION_CD)='" & [Forms]![Activity]![StreetNo] & "')..."

[pbaldy]

What exactly is the code now, and what exactly is the error? Can you post the db here?

[nvrwrkn]

argh...added them with same error. Everywhere I use the item.

Is this the problem? Like '" & [Forms]![Activity]![StreetNo] & "'+'*')

adding the wildcard on the end of a text box?

[nvrwrkn]

can't post the db here as there's quite a bit of construction I need to keep off the net not to mention I use linked oracle tables. The entire code as is (with a few names changed to protect the innocent) is:

Public Function GetThing()
Dim GeoData As DAO.Database
Dim Location As DAO.Recordset
Set GeoData = CurrentDb
Dim strsql As String

strsql = "SELECT TOP 1 CStr(CInt(GeoData!Area))+GeoData!Block+GeoData!Space " & _
        "FROM GeoData " & _
        "WHERE (((GeoData.EVEN_ODD_I)=IIf('" & [Forms]![Activity]![StreetNo] & "' MOD 2 = 0,'E','O')) And ((GeoData!STREET_DIRECTION_CD)='" & [Forms]![Activity]![StreetNo] & "') And ((GeoData!STREET_NME) Like '" & [Forms]![Activity]![StreetNo] & "'+'*') And (('" & [Forms]![Activity]![StreetNo] & "') Between GeoData!LOW_ADDRESS_NO And GeoData!HIGH_ADDRESS_NO)) "
        
   
   Debug.Print strsql

        
End Function

[pbaldy]

That might be a problem when the SQL tries to run, but it sounds like you're getting some sort of compile error prior to that.

[nvrwrkn]

hmmm.....in copying/pasting some of the code I repeated the same field, StreetNo. The form field checked against STREET_DIRECTION_CD is StreetDir, the STREET_NME like is given StreetName.

This failure to post the correct code on my part alerted me to the additional info you may need that both StreetDir and StreetName form items are ComboBoxes that are populated at the time I run the code. This works when I try this in a query but I'm not sure I can reference a combo box like I'm trying to do.

Could THAT be the prob? Combo boxes rather than text box referenced? The reason for the combo boxes is the street names are looked up while typing so's to insure the correct street name is always entered rather than misspellings.

[pbaldy]

Shouldn't matter, you can use combos too. Can you post an empty db with just that form in it? That might be enough to figure out what's causing the error. I must be blind but I'm not seeing it.

[nvrwrkn]

gonna be a while before I can get to that, what with everything else I have to do outside this app, plus this app. Lotta stripping and renaming of objects to fit what I've been posting. I could try starting a blank db and just doing this code and this form and see what happens.

[pbaldy]

Just so you know, you can simply export the form to a new db. You don't have to recreate it.