Auto populate form fields by clicking listbox row of lookup form

[ajitaccess]

Hello All
I have a request application made in Access 2016, it has following tables
tblRequestMain
-ID
-FirstName
-LastName
-Designation
-RequestDate
-Request
-Remarks

tblPersDetails (this holds data for personnels and serves lookup for above table)
-ID
-FirstName
-LastName
-Designation
Two Forms
1. frmRequestMain (with fields above in table tblRequestMain plus field FirstName also has a Search button which opens frmPersDetails)
2. frmPersDetails
- Listbox - lstSearch
-Textbox - txtSearch and button which populates lstSearch
Now what i am trying is to automatically populate FirstName, LastName & Designation fields on frmRequestMain by double clicking row on lstSearch.
Can anyone help with the code please.

Thanks

 

[June7]

Why are you duplicating data between tables? Just save ID from tblPersDetails into tblRequestMain.

 

[ajitaccess]

yes only ID of Name and ID of Designation will be saved in tblRequestMain but i need name and designation on Form frmRequestMain so that operator know whose request is being added.

 

[Minty]

That's a bad choice of field name by the way , ID of what ? You have 2 fields called ID change them to PersID and RequestID, then you'll know what you are referring to.

If you already are displaying the name fields in your list box I'm not sure why you would need to display them again? Simply set the ID field on the form to that of the list box.

What is the rowsource for the list box - what fields are you bringing in, and what is the bound column?

 

[ajitaccess]

That's a bad choice of field name by the way , ID of what ? You have 2 fields called ID change them to PersID and RequestID, then you'll know what you are referring to.

If you already are displaying the name fields in your list box I'm not sure why you would need to display them again? Simply set the ID field on the form to that of the list box.

What is the rowsource for the list box - what fields are you bringing in, and what is the bound column?

Hi Minty
Firstly the application opens with Request form which displays data as datasheet it has button for new entry which receives entry for new request i.e. frmRequestMain, that is where i need to lookup details of employee to enter further details. this datasheet has NameID, RankID and DesgID which have been set to show actual name in place of respective ID. Names, Rank and Designations are stored in separate table tables.
or please suggest better option. There are about 50 Ranks and 35 Designations among 20000 personnels.
The data is of approx 20000 employees and i am using list box as lookup form, as data entry operator was making mistakes in entering names and ranks. for example some place it was Managar/Manager, Parmod/Pramod etc.
This was creating wrong reports if i wanted to check how many times Parmod has requested or how many managers have requested.

 

[Minty]

Firstly a list box is probably no good if you have 20000 names to scroll through.
Secondly if the persons ID already has their designation set in the tblPersDetails, then there is no need to re-enter that again - simply pull that into the query it.

I would also add a tblDesignation with a list of your available designations and force that to be stored as the value in the person table. No typing errors at all then.
Ditto with the rank.

 

[ajitaccess]

Firstly a list box is probably no good if you have 20000 names to scroll through.
Secondly if the persons ID already has their designation set in the tblPersDetails, then there is no need to re-enter that again - simply pull that into the query it.

I would also add a tblDesignation with a list of your available designations and force that to be stored as the value in the person table. No typing errors at all then.
Ditto with the rank.

That all has been taken care of (as you suggested), and i am filtering listbox for Designation or ranks to narrow search. But still my original question is unanswered how to populate form fields with so that operator know whose data he is editing.
Please any answers towards that direction

 

[Minty]

Okay - so in your list box rowsource make sure you are bringing in the additional fields you need to display. Lets assume you have them in this order;
-ID - Hidden but the bound column
-FirstName -
-LastName
-Designation

So in your listbox click event you will want the following code - I will assume your controls on the form to be displayed are called txtFirstName, txtLastName and txtDesignation;

Me.txtFirstName = lstSearch.Column(1)
Me.txtLastName = lstSearch.Column(2)
Me.txtDesignation = lstSearch.Column(3)

 

[ajitaccess]

Private Sub lstSearch_DblClick(Cancel As Integer)
Me.txtName = Me.lstSearch.Column(1)
End Sub
i am writing this code but it is giving error method not found.
actually it is not recognizing text field on main form.
rather it is not prompting for any field on form frmRequestMain.
i even tried
Forms!frmRequestMain.txtName = Me.lstSearch.Column(1)
but this also didn't work.:banghead:

 

[Minty]

This may sound like a bit of a strange idea, but why not put the person search and list boxes on your main frmRequestMain header, they aren't bound to anything on the underlying data, just used to find an employee that is then stored on your Request data.

 

[ajitaccess]

Can elaborate or share sone sample please. That will be a great help.
Thanks in advance

 

[Minty]

It would probably be easier if you posted a stripped down version of your existing database.

Remove 99% of the data - just leave a couple of dummy employee records and request records, and the forms and tables.