Search Form to Search for Value in another Form

[AMBLY]

Hello ! Hope someone might be able to help me with this one. I run Access2000 on XP.

I have a form : frmONE- which contains a txt field: ctrCTN from my table/database. The values in ctrCTN are unique. Next to this field is a cmdFIND button

frmONE is open and the active window.
I want to search for values in ctrCTN, and go to the record which contains that value. I do not want to use a cmdButton with in-built Access Find procedure to FindRecord. And I don’t wasn’t to use a ComboBox as the field being searched will contain 25000+ records. I can utilize an unbound text box place on frmONE (which I found here http://www.databasedev.co.uk/text_search.html) but my frmONE is pretty full and I would prefer to use a second pop-up form, frmTWO.

ie:
FrmTWO: is a popup modal form, with:
an unbound textbox, txtEntry,
a cmdSearch button. whose On_Click Event procedure runs the search
a cmdClose button which closes the frmTWO

1) User on active frmONE clicks the cmdFIND button
2) FrmTWO opens, and User is asked to enter value to look for, in txtEntry.

IF: User decides at this point not to search after all:
3) User clicks cmdCLOSE, and closes the frmTWO., and focus returns to returns to frmONE, and say to fldCTN.

Otherwise,
User enters a value into txtEntry (on frmTWO)
4) The code will take the value entered and look for it in frmONE, in ctrCTN

IF: the sought value is found on frmONE, in ctrCTN, then the that record on frmONE receives focus, frmTWO will close and a message box will pop up saying “Record Found” (OK).

ELSE, IF The Value is not found, the focus remains on FrmTWO in txtEntry, and a message Box pops up saying “Record is not found, Please try again” (OK).

User is then left on frmTWO, and can either perform another search , or close the form (cmdClose) and return to frmONE.

I can play with the code (found for the textbox search link above,) to the point, where if a valid value is entered on frmTWO, the frmONE record with that value is retrieved and focus is in ctrCTN, and frmTWO closes ... Where I’m stuck is the coding for if the search value is not found, and I want to continue action on FrmTWO (ie another search, or close it).

Many thanks for your help!
AMBLY

[MMcCarthy]

Don't have frmONE open and Active. Create frmTWO to be a search form that will open frmONE to the specific record only

On frmTWO have an unbound textbox called txtctrCTN and a button called cmdFind

Put this code behind the cmdFind Button

 
Private Sub cmdFind_Click()
 
    DoCmd.OpenForm "frmONE", , , "[ctrCTN]='" & Me.txtctrCTN & "'"
    DoCmd.Close acForm, "frmTWO"
 
End Sub
 

This will open frmONE to the spedified record.

[AMBLY]

Thankyou for the reply and sorry for my late acknowledgement - I thought I would get an auto-email advising I had a reply to my question, but I didn't (unless I deleted it in error!!)......

Out of neccessity, the user is already in FRM1 (which has restricted password-only entry) when s/he needs to perform the search, so unless I've missed the plot (not unimaginable!) I think this code will not do what I want, unless I add an enter-password line to it, which would be a bit OTT (or perhaps a line to deactivate FRM1 while action is performed on FRM2 ???) ,,,,,but this code will certainly be useful to me in other areas.

Meantime, I have found a work-around - utilising show and hide methods of txt and cmd etc, which works well in it's environment.

Thanks again!
AMBLY

[MMcCarthy]

Thankyou for the reply and sorry for my late acknowledgement - I thought I would get an auto-email advising I had a reply to my question, but I didn't (unless I deleted it in error!!)......

Out of neccessity, the user is already in FRM1 (which has restricted password-only entry) when s/he needs to perform the search, so unless I've missed the plot (not unimaginable!) I think this code will not do what I want, unless I add an enter-password line to it, which would be a bit OTT (or perhaps a line to deactivate FRM1 while action is performed on FRM2 ???) ,,,,,but this code will certainly be useful to me in other areas.

Meantime, I have found a work-around - utilising show and hide methods of txt and cmd etc, which works well in it's environment.

Thanks again!
AMBLY

You're welcome

[AMBLY]

Hi Mary
I hope you can help me again :-)

Just rethinking the whole thing, based on your previous advice - I am now opening FORM1 to a specific record (via a search in it's field named: CTN) by having Users enter search criteria into a FORM2 unbound text box.

FORM2 TxtBox is called: txtCtrCTN)......I put a cmdButton called cmdFindCTN beside it via CommondBox Wizard. The resulting unadulterated code behind the button is as follows

--------------------------------------------------
Private Sub cmdFindCTN_Click()
On Error GoTo Err_cmdFindCTN_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FORM1"

stLinkCriteria = "[CTN]=" & Me![txtCtrCTN]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFindCTN_Click:
Exit Sub

Err_cmdFindCTN_Click:
MsgBox Err.Description
Resume Exit_cmdFindCTN_Click

End Sub
--------------------------------------------------------

Now, this works fine, BUT only if the User has searched for a valid CTN number on FORM1.

How (where?) do I amend the code to handle the situation where User has tried to find a CTN that does not exist....?? ie: if a match is not found, a msg box like:

MsgBox "Match Not Found For: " & stLinkCriteria & " - Please Try Again.", vbInformation, "Invalid CTN number!"

My inexperience with coding is showing! Hope you can help

Regards
AMBLY

[AMBLY]

I should also add....as the code stands at present:

If user has tried to find a CTN on FORM1 which does not exist, FORM1 still does open, but none of the fields are visible. So any code amendment, would have to cancel opening of FORM1, and just leave User on FORM2 with the desired msg box popping up.

Also, if a match is found, I want FORM2 to close when FORM1 is opened

Thanks!
AMBLY

[JConsulting]

I should also add....as the code stands at present:

If user has tried to find a CTN on FORM1 which does not exist, FORM1 still does open, but none of the fields are visible. So any code amendment, would have to cancel opening of FORM1, and just leave User on FORM2 with the desired msg box popping up.

Also, if a match is found, I want FORM2 to close when FORM1 is opened

Thanks!
AMBLY

easiest...

do a DCount using that CTN value...assuming it's in a table somewhere.

if DCount("*","thattable","CNT = " & me.CNT) then '<---add ticks ' ' if CNT is text
msgbox"Not found, try again"
exit sub
else
do your form open thing
end if

basically if the count returns 0, it's evaluated as false...kind of a shortcut thing.

let me know if this all makes sense to you
J

[AMBLY]

Hi J
Thanks for looking !

So, yes I can sort of understand...but sorry, I still need a bit of coaching as to where to put things!

CTN is a Primary Key/AutoNumber field in table named tblCOMPLIANCE.
It's format is 000. FRM2, the search form, has the Unbound Text Box also set as format 000.

So, on my Search FRM2, behind the Search button named cmdFindCTN, I put this code - which I'm sure I've got muddled with,

Private Sub cmdFindCTN_Click()
1. Dim stDocName As String
2. Dim stLinkCriteria As String

3. stDocName = "frmONE"

4. stLinkCriteria = "[CTN]=" & Me![txtCtrCTN]
' txtCtrCTN is the SearchBox on FRM2

'Add J's code here:
5. If DCount("*", "tblCOMPLIANCE", "CTN = " & Me.CTN) Then '<---no ticks ' ' because CTN is not a text fld
6. MsgBox "Not found, try again"
7. Exit Sub
8. Else
9. DoCmd.OpenForm stDocName, , , stLinkCriteria
10. End If

End Sub

What I get, if I try find a valid CTN number on FRM1 with this code , is Compile error - Method or Data not found on line 5. in the & Me.CTN), highlighting the .CTN bit.

I tried changing & Me.CTN), to & Me.txtCtrCTN), - ie: in my logic comparing table to Search Box (???) , but then what happens:
If I try find a valid number (is on table) I get the Line 6. msg Box.
If I try find an invalid number (is not on table) It opens FRM1 - but of course, there is no valid record match, so I just get the form design including Cmd butons that happen to be on it.... and no visible fields...

I'm sure I've done something daft!?

Cheers
AMBLY

[AMBLY]

Also - not sure if this makes a difference..

Whilst fld: CTN is on tbleCOMPLIANCE, the FRM1 (where we are trying to locate a valid CTN via this FRM2 SearchBox) is created from a Query based on tblCOMPLIANCE and one other tbl....?

Cheers
AMBLY

[130975]

Hi,
I have the same problem. So I was trying to adjust my coding. Is your problem Solved? If yes Please write the complete solution.