Display multiple records on single form 2

[markedback]

I am building a form (linked to single table) that displays the employee details of an employee. It is supposed to work as follows - user enters a last name in the lastName textbox on the form and clicks on a command button that triggers the event procedure which displays the details in various other field like first name, manager name and so on.

I am using dlookup in the event procedure.

-----------------------------------------------------------
StrEmpNumber = Nz(DLookup("[EmpNumber]", "Emp", "[last_nm] = '" & Me![last_nm] & "'"))
-----------------------------------------------------------

I would like the form to display all the employees details when there are employees with the same last name.

Currently, the form only displays the one record for the last name entered.

Please advice if it is possible to do this.

Thanks,
Mark.

 

[jamaarneen]

Hi,

It is possible.
in the click event of your command button, you should edit the 'source' of your form.
you could do it by creating a 'SQL string', where the WHERE clause is based on your textbox (like you did in your function).
and then set the record source of your form: Me.RecordSource = YourString

[there also a possibility to work with 'Filter']

Hope it helps

Ja

 

[kjv1611]

I'm assuming you're using a Continuous Form format rather than Single Form.

That's the only way [blue]Ja's[/blue] suggestion will work, I believe, and I believe it's the best/most efficient option.

By handling your recordset that way, there are no conditional statements to handle or anything. You just grab the data.

For more detail, I imagine it would go like this:

You have your field/text box, Me![last_nm], in the Form Header.

Then, the data fields you want to show will go in the Form Detail section.

Then you can either set the Form record source equal to the necessary query/SQL statement in the Form_Recorsource property, or else you set that in code. My suggestion would be that it is set in code upon the updating of the last_nam field in your form.

So it'd go something like this:

Private Sub last_nm_Update()
  Dim strSQL As String
  
  strSQL = "SELECT a.Field1 ,a.Field2 ,a.Field3 ,a.Field4 "
  strSQL = strSQL & "FROM Emp a "
  strSQL = strSQL & "WHERE a.[last_nm] = '" & last_nm.Text & "'"
  
  Form.Recordsource = strSQL

End Sub

Of course, I've not tested the code, so it may need some tweaking.

And it may not be possible at this point, but or ease of reading your own code, etc, ... for yourself and anyone who may have to work on this in the future if you are no longer at your current company... you may want to look at naming conventions, such as the Reddick Naming Convention, found here and here.

--

"If to err is human, then I must be some kind of human!" -Me

 

[jamaarneen]

Hi,

Mark: the explanation of kjv1611 is correct. and if you noticed, that way it will automatically update the frol right after the user ended to enter tha last name in the textbox (that means, when loosing focus), so you don't have to put a command button!

kjv1611: very nice explanation.
but be aware: you wrot:

You have your field/text box, Me![last_nm], in the Form Header

well, Me[red]![/red][last_nm] refers to a textbox on your form, while Me[red].[/red][last_nm] will refer to a field from your recordset, named [last_nm] (if there is one bound to your form).

Ja

 

[kjv1611]

Nice catch. I was merely copying/pasting from the OP question, however, which is why I had that format. Normally, I personally wouldn't include the [blue]Me[/blue] portion at all.

--

"If to err is human, then I must be some kind of human!" -Me

 

[markedback]

Ja, kjv, thanks for taking the time and effort to respond.

I continued to get just one record for each last name. Even in the continous form, the same record was displayed on all the subsequent forms.

But I tried a different approach. I created a query with a Last Name parameter and used it as the source for the form.

I now have the form I wanted. All the records with multiple last names can be viewed using the navigation buttons

Thanks again for your help,
Mark.