Split Text to Use in Future Search Button

[pjordan@drcog.org]

I have a form that displays street intersections and various things about them including coordinates. Because intersections can be either written as A & B or B & A, the database is set up to have a record for both variations (I know there is probably a better way to do this, but that's just how this database currently is designed with over 50,000 records).



I would like to somehow take the intersection Text Box (TXT_DRCOG_Loc which references the DRCOG_Loc field in the master table) and somehow split the text using the delimiter "&" and then have a button which then jumps to the record that has the streets flipped.

Example:
Record 1 shows has the TXT_DRCOG_Loc = "Alpha St & Beta Dr".

I now would like to press a button and have the FORM jump to the record that has TXT_DRCOG_Loc = "Beta Dr & Alpha St".

If it doesn't exist, then a msgbox can just state there is no record by that name.

Another thing is this database also contains addresses, so not every record will have a "&" in it. Those records will also not have 2nd record at all for it and that's fine. I might look into just having that Reverse Search button disabled for any record that doesn't contain a "&" in the DRCOG_Loc filed.

Not sure the best way to do this mutli-part idea of mine would be. I'm not very familiar with a lot of ACCESS coding so the less technical the better for me to understand how to do it and get it working...

Thanks

[ranman256]

What about a query:
[Intersect] like '*' & txtBox & '*'

Or 2 text boxes with a like on 2 streets?

[pjordan@drcog.org]

What about a query:
[Intersect] like '*' & txtBox & '*'

Or 2 text boxes with a like on 2 streets?

I was working on the 2 box idea, but wasn't sure how to get TXT_Loc_A to show anything before the "&" and TXT_Loc_B for the 2nd street using the data in the TXT_DRCOG_Loc box or the DRCOG_Loc field in the actual table.

[davegri]

Take a look at this:

DRCOG-davegri-v01.zip

[pjordan@drcog.org]

Take a look at this:

DRCOG-davegri-v01.zip

Great thanks, looked through it and think I have it figured out to try it. I did have to turn off the filter though once it went through as the original zip file didn't allow for the Reverse Lookup via FindFirst to work. Turning off the filter after a matching reverse record was found fixed the issue.

The Reverse Lookup via FindFirst is the only button I needed and think this will solve my question.

Thanks a gain and I'll let you know if I run into anything strange.

I did forget to mention due to my crosslinking the database with ARCGis, we currently have to use the older MDB format and I know sometimes the new accdb commands may not always work. But it seems that when I just converted it there wasn't any issues which is good...

[pjordan@drcog.org]

One question. Instead of having a warning saying there is no ampersand at all message (now the MSG reads No Reverse Locations Exist for Addresses), I'm trying to figure out how to just have the button disabled if the TXT_DRCOG_Loc doesn't contain an "&".

I've tried

Private Sub Form_Load()If Me.TXT_DRCOG_Loc Like "* & *" Then
Me.BTN_Find_FlipDRCOG_Loc.Enabled = False
End If
End Sub

But that doesn't do anything.

Also tried to add it into the button itself before it runs the script

Private Sub BTN_Find_FlipDRCOG_Loc_Click()    Dim sFlipped As String
    Dim rst As DAO.Recordset
    If IsNull(DRCOG_Loc) Then Exit Sub
    sFlipped = fcnFlip(DRCOG_Loc)
    If Len(sFlipped & vbNullString) = 0 Then
         BTN_Find_FlipDRCOG_Loc.Enabled = False
        'MsgBox "No Reverse Locations Exist for Addresses", vbInformation + vbOKOnly, "  A D D R E S S  O N L Y   "
        Exit Sub
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst "DRCOG_loc = '" & sFlipped & "'"
    If rst.NoMatch Then
            MsgBox "This Location Has not been Reversed Yet"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close
    Set rst = Nothing
End Sub

Neither seem to work. Also tried a few other examples I have found on the web (can't recall right now which ones they were)
, but nothing seemed to work...

[davegri]

The Form_Load event only looks at the first record. To have code to look at each record as it appears on the form, use the Form_Current event.
Try adding this form_current event sub.

Private Sub Form_Current()
    Dim nAmploc As Long
    nAmploc = Nz(InStr(DRCOG_Loc, "&"))
    If nAmploc = 0 Then
        cmdReverseFindFirst.Enabled = False
    Else
        cmdReverseFindFirst.Enabled = True
    End If
End Sub

[pjordan@drcog.org]

Thanks again, that worked perfectly. I think I did use the same thing i used on the form load as well, but didn't get it working either. Thanks for explaining though how the Form_Load works compared to current.

Thanks for solving my issues here...

[pjordan@drcog.org]

Ok just realized after testing this out, that I forgot an important feature in the search. Sometimes we have the same intersection, but a different record with the same intersection name, but in a different county (when the county line goes down the intersection).

How would I get the Find Reverse Intersection (fcnFlip) to also include the same COUNTY_NAME when it's being reversed? Would I add something like:

Private Sub BTN_Find_FlipDRCOG_Loc_Click()    Dim sFlipped As String
    Dim rst As DAO.Recordset
    If IsNull(DRCOG_Loc) Then Exit Sub
    sFlipped = fcnFlip(DRCOG_Loc) and where COUNTY_NAME = COUNTY_NAME
    If Len(sFlipped & vbNullString) = 0 Then
        MsgBox "No Reverse Locations Exist for Addresses", vbInformation + vbOKOnly, "  A D D R E S S  O N L Y   "
        Exit Sub
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst "DRCOG_loc = '" & sFlipped & "'"
    If rst.NoMatch Then
            MsgBox "This Location Has not been Reversed Yet"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close
    Set rst = Nothing
End Sub

Sorry about that, you always end up finding something you missed when testing things out. Other than that, this works great...

[davegri]

How would I get the Find Reverse Intersection (nAmploc) to also include the same COUNTY_NAME when it's being reversed?

Not having any idea of your tables or form, I can't respond. I need more input.

[pjordan@drcog.org]

Here's a sample of the table and the FORM I am using:

[davegri]

Need some clarification. You original request was to SEARCH for records with reversed streets, but the supplied DB CREATES records with reversed streets.
Can you explain what you are trying to accomplish overall?

[pjordan@drcog.org]

Need some clarification. You original request was to SEARCH for records with reversed streets, but the supplied DB CREATES records with reversed streets.
Can you explain what you are trying to accomplish overall?

Yes, originally I had forgotten that some intersections can be in two counties so at times we can have multiple of the same locations that are also in different counties.

I'm trying to get that new search button to not only find the reversed intersection (which is already done), but to make sure it searches for that exact county as well that the original intersection is in as well.

The data base has both COUNTY_NAME and DRCOG_Loc as primary keys because we don't want duplicates.

Currently the button that you helped me with will search for the next matching reverse record, but that doesn't always mean it's the correct record if the county is different. I need the counties to match up as well...

Hope that clears it up a bit...



An example is having the following:

ID: 1
DRCOG_Loc: First St & Second Ave
COUNTY_NAME = ALPHA

ID: 2
DRCOG_Loc: First St & Second Ave
COUNTY_NAME = BETA


ID:98
DRCOG_Loc: Second Ave & First St
COUNTY_NAME = ALPHA

ID:99
DRCOG_Loc: Second Ave & First St
COUNTY_NAME = BETA

Doing the reverse search on ID: 1 will pull up record 98 rather than ID:99 like it should as ID:98 is the next in the search order. I need it to take the COUNTY_NAME into account in the search...

[davegri]

OK. See if this does the trick.

Private Sub BTN_Find_FlipDRCOG_Loc_Click()
    Dim sFlipped As String
    Dim rst As DAO.Recordset
    If IsNull(DRCOG_Loc) Then Exit Sub
    sFlipped = fcnFlip(DRCOG_Loc) 'This function is defined above as a public function
    If Len(sFlipped & vbNullString) = 0 Then
        MsgBox "No Reverse Locations Exist for Addresses", vbInformation + vbOKOnly, "  A D D R E S S  O N L Y   "
        Exit Sub
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst "DRCOG_loc = '" & sFlipped & "'" & " AND County_Name ='" & txtCountyName & "'"
    If rst.NoMatch Then
            MsgBox "This Location Has not been Reversed Yet"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close
    Set rst = Nothing
End Sub

[pjordan@drcog.org]

Nope, it just executes the rst.NoMatch with the MsgBox for every one now regardless if there is a match or not

   If rst.NoMatch Then
            MsgBox "This Location Has not been Reversed Yet"

UPDATE: Yes it does work, I just had to change the TxtCountyName to TXT_CountyName and it worked.

Thanks again for your help. Hopefully this should be it...