Update Parent form!

[kaakaoo]

Hi all,
I'am a newbie in Access so now I have a trouble with subform.
I have a table Employees and a form frmEmployees to input information into table. I also created a subform fsbEmployeeDetails (viewed in datasheet) to display all Employees and is a child of frmEmployees. Now I want to display the data on mainform whichever I click to record row on subform, How can I do it?
Please feel free to help me.
Thank you in advance!

 

[wiklendt]

you've got it a little backwards. a subform should be used when the record from the main form has many sub-records (as it were) to display, NOT to display more records of similar nature to the main form.

that is, if you have frmEmployee, then a subform might show data related to just the selected employee - e.g., hours worked.

i'd recommend using a listbox on the main form, which is powered by a query based on the same table as you have your form.

when you place a listbox on your form using the wizard, it may ask you what you want to do with the form, if you have three options, choose the last one (find records on the form based on my selection - or similar).

if the wizard doesn't let you do that, then just put the listbox there anyhow, and on the "after update" event of the listbox, put this code in:

Private Sub lstEmployees_AfterUpdate()
    
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EmloyeeID] = " & Str(Nz(Me![lstEmployees], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

note that this code requires that you have the employee primary key in the form's source AND as the BOUND column (which can be made hidden from the user by making it 0cm width) of your listbox.

 

[lj1602]

Hi kakaooo

I do tend to agree with wiklendt that you've got the principles the wrong way around, a subform should display details based on a main form selection.

But, to achieve what you want the code below, used on an Enter event for the primary key field on the subform (assumed here to be employee ID) will work. I'm also assuming that when you set up the subform you didn't link it to the main form, that is all of the employees show in the subform not just one.

The principle here is to hold a unique value for the employee in question when a subform record is clicked on and then use that in a SELECT query to change the recordsource of the main form to show the records for that employee only. Select the subform primary key field properties and enter this code against the On Enter event.

'Set up a procedure to run whenever you enter (click on) a
'record in the subform datasheet that will change
'the recordsource of the main form.
'This will display the respective employee data in
'any bound fields on the main form.
 
Private Sub Employeeid_Enter()
 
    'this is to hold the SQL SELECT query for the main form
    Dim strSQL As String
 
    'this is to hold the employee id for the select query (assuming it's an autonumber)
    Dim intID As Integer
 
    'set the employee id from the record clicked on
    'in the datasheet sub form
    intID = Forms!frmEmployees.fsbEmployeeDetails.Form.employeeid
 
    'create the SELECT query for the main form record source
    strSQL = "SELECT * FROM tblEmployees WHERE tblEmployees.employeeid = " & intID & ";"
 
    'set the main form record source to the data returned
    'by the SELECT query
    Forms!frmEmployees.Form.RecordSource = strSQL
 
End Sub

 

[kaakaoo]

Hi wiklendt and lj1602,

Thank you very much for your advices and codes. I showed my example as well as the way to practice, based on your guide I will apply the code to other forms. Following to lj1620's instruction I have done and it working well.
And now I have other issues need your help.
A Query (named qryInputData) built on relationship of some tables: tblEmployee, tblPrimeData that included some fields: txtDateinput, txtEmployeeID... A form (named frmInputData) designed to base on qryInputData using to input the data. I created a button (New) on the form for the new session inputting and Save button to save the data. As usual when New button clicked, all fields on the form will be clear and I can going on to finish and Save button will do last. However, one Employee might has a lot of data to input at the same time, so I want whenever Save button clicked, the information in txtDateInput and txtEmployeeID keeping intact for the new session and other fields be clear for inputting.
Could you please give me some suggestion?
Thank you.

 

[wiklendt]

Hi wiklendt and lj1602,

Thank you very much for your advices and codes. I showed my example as well as the way to practice, based on your guide I will apply the code to other forms. Following to lj1620's instruction I have done and it working well.
And now I have other issues need your help.
A Query (named qryInputData) built on relationship of some tables: tblEmployee, tblPrimeData that included some fields: txtDateinput, txtEmployeeID... A form (named frmInputData) designed to base on qryInputData using to input the data. I created a button (New) on the form for the new session inputting and Save button to save the data. As usual when New button clicked, all fields on the form will be clear and I can going on to finish and Save button will do last. However, one Employee might has a lot of data to input at the same time, so I want whenever Save button clicked, the information in txtDateInput and txtEmployeeID keeping intact for the new session and other fields be clear for inputting.
Could you please give me some suggestion?
Thank you.

i'm not sure i understand - do you mean that you have two text fields and each time the save button is pressed it appends more and more text to the field, rather than replacing the text that was there?

 

[kaakaoo]

i'm not sure i understand - do you mean that you have two text fields and each time the save button is pressed it appends more and more text to the field, rather than replacing the text that was there?

Hi wiklendt
It meant that, after full filled infor into all textbox or combo box on the form, I must click Save button to store all and the the form will refresh as like as click New button so bring about all textbox be blank for new inputting. Now I want when Save button pressed, two fields txtInputDate and txtEmployeeID (As I mentioned above) keeping intact infor as same as the last. For instance, today I input the data for Emplyee Id 1234 on 24/10/2009, I will choose input date is 24/10/2009 in txtInputDate and ID 1234 in txtEmployeeID field and something following. Whenever all fields filled, I click Save button and going to next input. But on 24/10/2009 I need to input more info regarding to ID 1234, for save time I dont want to re-input Date and this ID in Date field and ID field, that means 24/10/2009 and 1234 still exist in Date and Id textbox.
Sorry for my mention so long but I get stuck to explain in short. Please feel free to understand.
Thank you

 

[wiklendt]

looks like you might need to normalise your tables first OR (if your tables ARE normalised) then redesign your form slightly - if you want the same employee and more data, then that "more data" should be in a 'subform' (which, if your tables are normalised, means that this 'more data' is in a separate table to your employee table)