Could be a newbie question on Forms 2

[paulwood]

I normally do battle with ASP on the Internet, but none the less I cannot believe I am having problems with this.

I have a table with names and adresses and stuff which is displayed in a regular form, we'll call it MyForm1. I need to search it for a postcode which is chosen from a dropdown box in another form, we'll call it MyForm2. In the UK there are only 121 unique town postcodes, some one character, some two. Anyway I have got the search thing working fine, it finds a postcode in the table and a message box pops up to let me know it has. So far, so good. Trouble is, no matter how hard I try, I can't shift the focus onto the previous form and show the record which matches the postcode.

I thought all I had to do was use SetFocus and DoCmd.GoToRecord for it to work. Obviously not. Any ideas?

 

[Remou]

In this example, form 2 is opened with OpenArgs set to "Form1"

    Dim rs As DAO.Recordset
    Dim txtFormName As String
    
    txtFormName = Me.OpenArgs
    Set rs = Forms(txtFormName).RecordsetClone
    rs.FindFirst "PersonKey=" & Me.lstNames
    Forms(txtFormName).Bookmark = rs.Bookmark

 

[paulwood]

It may be that I should mention a few other things about the forms. The main form (MyForm1) with all the data showing has a button on it which opens MyForm2 for searching purposes. MyForm2 only has the dropdown list of postcodes on it. The searching is done via VBA using a Do While construct which searches through the records in the table behind MyForm1 and comes up with a match if there is one. There may be easier ways of doing this, it's just the way I learnt before I had to use VBA for this project. This means that there is no match between anything on the active form with only the drop down list and the already open form I want to display the matching record in. I therefore can't see where the

rs.FindFirst "PersonKey=" & Me.lstNames

bit comes in. Apologies, but it's gone 11pm here and my brain has turned to mush!

 

[Remou]

Perhaps you could post some of your code? The example I posted is used with a form that lists names. Clicking a name in the listbox causes the main form to display the record having that name. It seemed to me that this was close to what you wished to do.

 

[paulwood]

Oh, I see. In your example I am right in assuming that PersonKey is the field to be checked in the table and lstnames is the result of choosing a specific postcode from the dropdown. This would certainly be more elegant than the rather long-winded way I have done it. I will see whether I can implement this and if not, get back to you with the code. Most probably tomorrow now when brain working!!

 

[paulwood]

I am far from an expert, so if this is a dumb question, please be kind! I thought the OpenArgs could only be used when opening a form, and I want the focus to return to a form that is already open behind the search form. One other thing, the postcodes are unique but there is a problem that some one character codes are part of another. So the code for Sheffield is S and the code for Southampton is SO. If just S is searched for, then SO will come up as well as a number of other two-character codes beginning with S. Annoying huh? I have therefore added some code to the search code I originally used which deals with this, but that can't be added to the code you suggested. Code I am trying to get to work below.

Private Sub Command5_Click()
Dim stLinkCriteria As String
Dim objDB As Database
Dim objRS As DAO.Recordset
Dim objRS2 As DAO.Recordset
Dim stDocName As String
Dim mySQL As String

Dim success_flag As String
Dim intResponse As Integer
Dim Title As String
Dim first_name As String
Dim last_name As String
Dim conid As String 'Unique numerical id for each record
Dim ad_pc As String 'Variable which stores the 1 or 2 digit code

success_flag = 0
intResponse = 0
ad_pc = 0

Set objDB = CurrentDb()
' Table with contact details in it
Set objRS = objDB.OpenRecordset("SELECT * from RM_Tbl_Advocates_FmQry", dbOpenDynaset)

Do While Not objRS.EOF
' Checks for existence of code as some non-UK records don't have one
If objRS.Fields("postal_code") <> "" Then
' Checks length of code and assigns value to variable
If Len(Me![Combo12]) = 2 Then
ad_pc = Mid(objRS.Fields("postal_code"), 1, 2)
Else
ad_pc = Mid(objRS.Fields("postal_code"), 1, 1)
End If
End If

'Checks for matching post code Combo12 holds list of codes
If ad_pc = Me![Combo12] Then

Title = objRS.Fields("Title")
first_name = objRS.Fields("FirstName")
last_name = objRS.Fields("LastName")
conid = objRS.Fields("con_id")

'Saves matching contact details into temp table just in case
DoCmd.SetWarnings False
    mySQL = "DELETE * FROM advocates_postcode_temp"
    DoCmd.RunSQL mySQL
    mySQL = "INSERT INTO advocates_postcode_temp (con_id, Title, FirstName, LastName, PostCode) VALUES ('" & conid & "','" & Title & "','" & first_name & "','" & last_name & "','" & ad_pc & "');"
    DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
success_flag = 1
'This is where the focus needs to change to the other form to show matches
    
intResponse = MsgBox("There is a matching Post Code" & vbCr & "Do you want to search further?", vbYesNo, "Post Code")

'If not the required record then look again

If intResponse = 6 Then
    objRS.MoveNext
End If
If intResponse = 7 Then
Exit Do
End If
Else
    objRS.MoveNext
End If
Loop

If success_flag = 0 Then
'If no match found, displays message
intResponse = MsgBox("There are no matching Post Codes", vbYesNo, "Post Code")
Else
'Other stuff
End If
objRS.Close
Set objRS = Nothing

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click

End Sub

 

[Remou]

The main point of the example I posted was that it used the recordsetclone of form1, which means that a bookmark could be set for the form. The OpenArgs bit just makes the code re-usable.

To find on form1, try:

'Recordset for Form1
Set rs = Forms!Form1.RecodsetClone
'Find postcode
rs.Findfirst "PostCode='" & PostCode & "'"
If Not rs.NoMatch
  'If found, use the bookmark properties to 
  'synchronise rs with Form1 recordset
  Forms!Form1.Bookmark=rs.Bookmark
End If

 

[paulwood]

Kinda getting there I think, thanks so far! Problem now is the one-digit codes. If a one-digit code is chosen, it doesn't differentiate between that and a two digit so as my previous example, choosing the S code could match an SO entry. The postcode is not just the first one or two letters, it is typically AANN NAA or ANN NAA. I have tried using the Mid function, but it doesn't seem to make any difference in this context.

If Len(Combo12) = 2 Then
rs.FindFirst "Mid(postal_code, 1, 2)='" & Combo12 & "'"
Else
rs.FindFirst "Mid(postal_code, 1, 1)='" & Combo12 & "'"
End If

Also it may need to check again for a further match for the same code if the first match is not the required one.

 

[Remou]

If you want:
S returns ASA, AAS and SAA
SA returns ASA and SAA

You can use Like
[tt]rs.FindFirst "Postcode Like '*" & Me.Combo12 & "*'"[/tt]

To find the next entry, do not close the search form and provide a Find Next button:
[tt]rs.FindNext "Postcode Like '*" & Me.Combo12 & "*'"[/tt]

Other options include:
1. Returning a list of all found items to a list box and allowing the user to click through them to select the required record
2. Filtering the main form to only include found items.

I quite like using a small pop-up with a search box and a listbox. The more characters that are typed into the search box, the shorter the list. The user can then click though the list to see the detailed records.

 

[paulwood]

Almost perfect, but the Like needs to be limited to the first character of the postcode in the field being searched if the code chosen for searching on is single alphabetic character, and limited to the first two characters if the code searched for is two alphabetic characters.

Example;
If someone wanted to find people in Sheffield and surrounding district, they would search for all the records with a postcode beginning with S. An acceptable postcode would be S20 9RU whereas an unacceptable postcode would be SO21 5HR because that is in or around Southampton. It is just the alphabetic characters at the start of the postcode which are relevent.

BTW I like your idea of gradually zeroing in using the search/listbox thing. Wouldn't help here, but would look pretty cool and be real user friendly!!

 

[PHV]

And what about this ?
rs.FindFirst "postal_code Like '" & Combo12 & "#*'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[paulwood]

That limits it to matching just the first two places, but I am afraid doesn't solve it. It means that if I search for Liverpool (L) I can get Plymouth (PL) in the results. When it is a single character postcode searched on it has to ignore any postcode with two alphabetic characters at the start.

 

[paulwood]

Oops, my error. I left an * in where I shouldn't have. It works! Thanks so much you guys.