Using FindRecord to jump to Specific Records with Criteria

[pjordan@drcog.org]

Did some research and am not able to understand how to accomplish this (I am sure it's pretty simple).



I have a form (called MASTER_FORM) and its just a simple form that has Location data (address, County, Longitude and Latitude, etc).

The records all have the location and County and City imported in by another database, but we need to populate the exact LONG and LAT for these locations. So as time goes by, the database is filled with records that have LONGITUDE and LATITUDE populated. My question is I want to make a simple search button (assume using the DoCmd.FindRecord where the LONGITUDE is Null. That way the user can easily open the database, and on the Form just press that button to go to the next record that needs to be updated.

I've been playing around with the syntax and keep getting "Compile Error: Argument not optional"

Here's what I'm using (I am keeping the defaults in so I know I have the right number of variables). However I feel NULL shouldn't be in quotes, but I can't seem to get it to work either way.


DoCmd.FindRecord , LONGITUDE="Null", acEntire, False, acSearchAll, False, acCurrent, True

Thanks

[ranman256]

Dont use FINDRECORD, instead FILTER the records by using a continuous form to show all records,
then when the user fills in the boxes, then clicks the FIND button, use vb to build the where clause:

sub btnFind_click()
dim sWhere as string

if not IsNull(txtBox1) then sWhere = sWhere & " and [field1]='" & me.txtBox1 & "'"
if not IsNull(txtBox2) then sWhere = sWhere & " and [field2]='" & me.txtBox2 & "'"
if not IsNull(txtBox3) then sWhere = sWhere & " and [field3]='" & me.txtBox3 & "'"

if sWhere = "" then
   me.filterOn = false
else
    sWhere = mid(sWhere,5)       'remove the 1st 'and'
   me.filter = sWhere
   me.filterOn = true
endif
end sub

[pjordan@drcog.org]

Ok so how do I do this when the field LONGITUDE is a Double Number and not actually a Text? Since I keep getting the Data Mismatch Error​? Oh and the TXT_Long, the TXT part refers to the type of object in the FORM not that it's actually a text field...

Thanks.

Dim sWhere As String


If Not IsNull(TXT_Long) Then sWhere = sWhere & " and [LONGITUDE]='" & Me.TXT_Long & "'"


If sWhere = "" Then
   Me.FilterOn = False
Else
    sWhere = Mid(sWhere, 5)      'remove the 1st 'and'
   Me.Filter = sWhere
   Me.FilterOn = True
End If


Exit_BTN_BlankLoc_Click:
    Exit Sub


Err_BTN_BlankLoc_Click:
    MsgBox Err.Description
    Resume Exit_BTN_BlankLoc_Click
    
End Sub

[aytee111]

Use quotes for text fields, no quotes for number fields, # for date fields.

Test it by creating a new query and adding it as a criteria. Look at the SQL view and it will show you the exact format to use for your filter.

[pjordan@drcog.org]

Use quotes for text fields, no quotes for number fields, # for date fields.

Test it by creating a new query and adding it as a criteria. Look at the SQL view and it will show you the exact format to use for your filter.

Yes I was trying to remove the quotes and it doesn't work. Tried various options as I knew they have to be removed somehow, just not sure how. Plus it won't work in SQL from a new Query as it says the functions are supported in this version (using Office 365)...

[aytee111]

and [LONGITUDE]=" & Me.TXT_Long
This doesn't work in 365?

[pjordan@drcog.org]

Well my mistake was I used a different code by accident. However trying to just use If Not IsNull(TXT_Long) Then sWhere = sWhere & " and [LONGITUDE]='" & Me.TXT_Long & "'" doesn't get me anywhere because of the ' that are in the script above. Not much of a programmer here...

[orange]

pjordan,

Do you have lat/Long coordinates for specific street addresses or just for city?
I found a file on internet ~ 40,000 records that shows

zipcode
city
state
Lat/long

But it doesn't show individual homes/businesses.

If you have access to the table with the Lat/Long and the table you are populating, why can't you use a query with both tables with a JOIN on the common unique field?

If I have misunderstood your requirement/issue, perhaps you could provide more details and clarification.

Good luck.

[pjordan@drcog.org]

pjordan,

Do you have lat/Long coordinates for specific street addresses or just for city?
I found a file on internet ~ 40,000 records that shows

zipcode
city
state
Lat/long

But it doesn't show individual homes/businesses.

If you have access to the table with the Lat/Long and the table you are populating, why can't you use a query with both tables with a JOIN on the common unique field?

If I have misunderstood your requirement/issue, perhaps you could provide more details and clarification.

Good luck.

The addresses we have are crashes done by the police, so often they give the house address. These locations using various geocoding never seem to accurately get coded properly (only intersections geocode correctly). So with these ones in this database we are building our own database. We used to have other options, but the prices of geocoding and the results haven't been that great. The big issue is the Zip Codes are not included and seems most if not all geocoding services now use that for better accuracy.

So currently to get to the next record with a missing value I just do a Find and Replace and search in the Longitude box for finding "is Null". But was just wanting a simple button to do that instead (as sometimes people might forget how to search for a null in the Find and Replace box. That was all.

I was thinking it would be a simple thing, but seems it's not as straight forward as I was hoping.

I even tried something like this without success:

If IsNull(TXT_Long) = False Then      Me.Recordset.FindFirst "[LONGITUDE]=" & ""
      If Me.Recordset.NoMatch Then
         MsgBox "All LAT/LONG are Populated", vbOKOnly + vbInformation, "NO MORE RECORDS NEED TO BE UPDATED"
      End If

I could just have a query to find all the records that have a blank LONGITUDE and then sort it and just use TOP 1 I guess. But was just hoping there was a quicker way. Hope that clarifies things...

[orange]

If you have addresses, you can get lat/long from Google Maps. There is a limit of about 2500 searches/lookups per day, but that doesn't seem to be an issue in your case.

[pjordan@drcog.org]

No and if you use the Google API like we tried out for geocoding, it actually gives inaccurate locations for many of the addresses without a Zip Code. Manually putting the address in Google however works. This has been the issue with using Google, ESRI, and a load of other goecoding sources.

I just made a simple Query to do the Top 1 for Null LONGITUDE and then the user can just copy the ObjectID and use the Search ObjectID button (or Find/Replace) for that number and quickly jump to it. I was just thinking there had to be an easier way in the Form to do this, but seems not...