find a record easily

[JBad]

hi..
i want the user to enter a employee code in a normal unbound textbox and search that code in the table (EmpMaster). If the record is found, display the record in the corresponding textboxes / labels (which ever is advisable).
Thanks
JB

 

[MajP]

One way.
faq702-3765

Modify it to use a textbox instead of a combo

 

[JBad]

hi MajP
i cut pasted the code in the FAQ in the AfterUpdate event of the txtBox1 as follows:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EMPNO=" & txtEcode.Value 'its numeric
If rst.NoMatch Then
MsgBox "The selected record can not be displayed because it is filtered out. " _
& "To display this record, you must first turn off record filtering.", _
vbInformation
Else
Me.Bookmark = rst.Bookmark

End If
Set rst = Nothing

But on running, it gives following error:

'You entered an expression that has an invalid reference to the RecordsetClone property'. ???

Also, if the record is found, how do i display the rest of the items like Empname , Date of Birth etc. ..how do i assign the exact details to the txtBox2,txtBox3... .values?

Thanks...

 

[MajP]

You did not specify, but I assume now that the form is unbounded.

If that is the case then this approach does not work.

Why not bound the form to the query or table? That will make it easier. If the forms is unbounded then I assume you would get this error:

you entered an expression that has an invalid reference to the RecordsetClone property'.

Because there is no recordset.

You can do this unbounded, but why would you want to?

 

[JBad]

Hi MajP

yes u r right, the form is unbounded. I hadnt thought of binding it to the table. It was pretty stupid on my part. Upon binding it and now using the combobox (as specified in the FAQ ), the problem is how to display the rest of the details of that corresponding record? I am attaching the code which i thought should work but didnt...(its not giving any error!)

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "EMPNO=" & cboEcode.Column(0)
If rst.NoMatch Then
MsgBox "No Record Found!", vbInformation
Else
Me.Bookmark = rst.Bookmark
txtEname.ControlSource = DLookup("[EMPNAME]",_
"LAB_MASTER_1", "EMPNO=" & _
Forms!cboEcode.Column(0))
End If
Set rst = Nothing

The ideology is to make the user select / enter a employee code and depending upon if its found in the table, display the details of that employee. The problem is i cant get how to display the details of that found record...i mean how should i assign the value of table fields of the found record to the textboxes / labels (which do u advise?)

Thanks...

 

[MajP]

If the details are in the bound recordsource then Bind the controls to the record like any bound form. If they are not in the underlying record then explain your table structure a little bit.

Not sure how you are using this form, but if it is display only You might want to make the form uneditable, allow additions false, allow deletions false, and lock the fields

 

[JBad]

Hi MajP
The record is in the same table i m using to bind the form with. But dont u think that upon binding the controls (textboxes or whatever) which would b displaying the data, the first record would b shown there by default (like when the form is loaded) ? The table is pretty simple :

EmpNo as Double (or shoud i use Integer cuz its only 5 digits)
EmpName as Text
DOB as Date
PassportNo as Text
PassportExpDt as Date

Thanks.. JB

 

[MajP]

You always want to limit the scope, life, and size of your variables. This is good coding practice.

Integer
Size: 2 bytes
Values: -32,768 to 32,767

Long (long integer)
4 bytes
-2,147,483,648 to 2,147,483,647

Double(double-precision floating-point)
8 bytes
-1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

So an integer requires 2bytes and a double requires 8 bytes. However if you are going beyond 32,767 then use a 4 byte long which goes up to 2,147,483,647

Again, I am not sure how the user interacts with this form, but I am hearing that when the form opens it should be a blank record. One way to do this is to set the form to
DataEntry = True
in its properties. This opens and shows only a new blank record. Now in your code add to the very beginning:
Me.DataEntry = False
So the forms switches to a "show all" mode once they start looking for something.

Again it depends how you use the form. If this is for display purposes only then you could also go back to an unbound form. Each textbox using a dlookup function that references the EMPNO. Something like this for the empName textbox

=dlookup("empName","tblEmployees","EMPNO=" & cboEcode.Column(0))

 

[ProgramError]

An alternative method would be to bind the form to a query and prompt for the comparator ie the employee code.


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[MajP]

That is a better idea. Use a query and if you want to use your combo box then you can put something like this in your criteria for the EmpNO
[Forms]![YourFormName]![cboEcode].Column(0)

 

[jmgaddis]

Might be a silly question....

But are the additional fields you want to see shown on the form?

Once you have bound the form to a table or query, in design view, simply drag the desired fields from the fields list that you which to see on the screen.

Once the recordset or Bookmark is moved it should display the desired fields.

Hope this helps.

Thanks,
John

 

[YoungManRiver]

All,

Do I assume the reason for using "RecordSetClone" is to try to allow for multi-user use of the application?

YMR

 

[MajP]

The .recordset returns a pointer to the recordset. Any change you make effects the forms recordset. i.e. you move in the recordset the form moves. The .recordsetclone is a copy of the recordset (hence the name clone). You can change and work with it and not effect the forms recordset.