FIND a record using a form & reporting to a form ACS 97

[murphysdad]

Yep,
I am trying to have a form (frmFind) with a text box(txtSearch)and command button(cmdFind) look up a record by using the controlNumber[this is the name of the column in the table(tblmainFTag)] that is supposed to be entered into the text box on the form.

I would like them to enter the controlNumber and hit the cmd button...POOF then it brings up that record in form view. I would like the record to come up in the form they use to add new items(frmMainFTag).

So I have the find form(frmFind)
table where to look it up(tblMainFTag)
form to where results should go(frmMainFTag)

I will take any help!! Rather new at code.

Thank you!

 

[oharab]

Do you want just the seleceted record to appear in frmMainFTag, or do you want all records available, with just the found record selected?

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[murphysdad]

I would like to have all of my available info. the reason why is because they have to update any missing information.

Jere

 

[SBendBuckeye]

Do something like the following in the Before Update event for the txtSearch control. Use Before Update because you can validate their entry and pop a message if they entered bad data.

Private Sub txtSearch_BeforeUpdate(Cancel As Integer)

If DCOUNT("ControlNumber", "tblmainFTag", _
&quot;ControlNumber = '&quot; & txtSearch & &quot;'&quot = 0 Then <- Text
&quot;ControlNumber = &quot; & txtSearch) = 0 Then <- Numeric
Msgbox &quot;Invalid Control Number: &quot; & txtSearch, _
vbOKOnly + vbInformation, &quot;Data Error&quot;
Cancel = True
txtSearch.Undo
Exit Sub
Else
DoCmd.OpenForm &quot;frmMainFTag&quot;, acNormal, , _
&quot;ControlNumber = '&quot; & txtSearch & &quot;'&quot;, acFormEdit, _
acDialog
End If

End Sub

Here is what is happening above. First it uses the function DCOUNT to ensure that you have a valid control number. If ControlNumber is a numeric field then remove the single quote &quot;'&quot; around the expression as in the second line above.

If they entered an invalid ControlNumber then a message box pops up with an OK button as their only option. When they click it, their number is removed and Cancel returns them back to txtSearch to re-enter a new ControlNumber.

If it is valid, it opens up the form frmMainFTag in normal edit mode as a dialog. Opening it as a dialog pauses your form frmFind until you close frmMainFTag or make it invisible. I have never tried the Where Clause part so if that doesn't work, repost and we'll figure it out together.

By the way, the same thing holds there as in DCOUNT above. If ControlNumber is a string, use single quotes &quot;'&quot;, otherwise do not.

Good Luck!

 

[murphysdad]

Thank you for the respond. I am getting a syntax error for the following statement:

Msgbox &quot;Invalid Control Number: &quot; & txtSearch, _
vbOKOnly + vbInformation, &quot;Data Error&quot;

What do you think?

J

 

[SBendBuckeye]

Try this:

Msgbox &quot;Invalid Control Number: &quot; & Nz(txtSearch), _
vbOKOnly + vbInformation, &quot;Data Error&quot;

All it was trying to do was display the invalid txtSearch entry. If it doesn't work for some reason, just leave off the & txtSearch part.

Good Luck!

 

[murphysdad]

THAT WORKS INCREDIBLY!!!
Thank you so much!

Jeremy