Another Loading an Unbound Subform 1

[pokeymonsc]

I have a parent form linked to a multirecord unbound subform.
I am using the following code to load the subform:

Dim rs As DAO.Recordset
Dim QDef As QueryDef
Dim tmpID As Integer

tmpID = Forms!SalaryBenefits!txtEmpID.Value

Set QDef = CurrentDb.CreateQueryDef("", "select * from dbo_payperiodcharges where empid = " & tmpID & " and payperiod between #01/01/2004# and #3/30/2004#")
Set rs = QDef.OpenRecordset
counter = rs.RecordCount
If counter > 0 Then
Do Until rs.EOF
Me.PayPeriod = rs.Fields![PayPeriod]
Me.HoursWorked = rs.Fields![HoursWorked]
Me.HourrsInvoiced = rs.Fields![HourrsInvoiced]
Me.ChargeDebit = rs.Fields![ChargeDebit]
Me.ChargeCredit = rs.Fields![ChargeCredit]
Me.ChargeOther = rs.Fields![ChargeOther]
Me.Balance = rs.Fields![Balance]
Me.Comments = rs.Fields![Comments]
Me.Check22 = rs.Fields![Verified]
Me.RecordID = rs.Fields![RecordID]
Me.EmpID = rs.Fields![EmpID]
rs.MoveNext
Loop
End If

Stepping through the code I get 13 subform records for the first EmpID (which is the correct #) But --- and here's my problem: On the form itself I have displayed the last record 13 times, not 13 records 1 time each. It's almost as if I need to have a me.movenext but there isn't any. Any suggestions out there?

 

[Zion7]

pokeymonsc, it's redundant that you loop & add values to your subform. You said the 2 forms were linked already, Parent & child. All you need to do is, take your query def statement & put it on the recordsource of the subform.


Forms!SalaryBenefits!dbo_payperiodcharges.Form.Recordsource = "select * from dbo_payperiodcharges where empid = " & tmpID & " and payperiod between #01/01/2004# and #3/30/2004#"

That should be all you need to do. Your form is unbound, so all the records are repeating otherwise. But the link, is showing the right number of records. (If I'm not mistaken).

Good Luck!

 

[pokeymonsc]

Thanks dboulos:

I was unsure of where to put your code - so I put it in 'Private Sub Form_Current()' Did you mean to reference both the Master and the Sub form? e.g. 'SalaryBenefits'(master) and 'payperiodcharges'(Sub)? If you did I got an error "Can't find field Payperiodcharges". (As if dbo_payperiodcharges were a field in SalaryBenefits) If not I modified the code to read:
me.Form.Recordsource = "select * from dbo_payperiodcharges where empid = " & tmpID & " and payperiod between #01/01/2004# and #3/30/2004#" and stuck it in the dbo_payperiodcharges module in 'Sub Form_Current'. I didn't get any error but I didn't get any fill of the subform either. Just a blank form.

Lew

 

[pokeymonsc]

I take it back. I had the sub-form open in design view when I tried to run it and that's why the sub-form on the main form appeared blank. Your way does work and it gives me the right data. Onliest thing. There is a very perceptable flicker and a noticible lagtime in producing the results that wasn't there before. ????? Any suggestion?

 

[Zion7]

Sorry pokeymonsc, I assumed, as your original posts shows, that you were using the OnLoad event, which is appropriate.
the OnCurrent event, for such a procedure, may be causing the flickering?
Outside of the flickering, is evertything else working fine? I'm just curious as to what your objective is. Maybe there's still a better way, to achieve your goal?

 

[pokeymonsc]

dboulos: Thank you for your interest and help:
Form works great for the first load with your code in 'Form_Load', but I get no data when I click on the next record button: because the form is not 'loading' this time.

I'll try to be brief in describing what I'm trying to accomplish: My master form (& table) contains employee data, it is linked (via EmpID) to a child form which contains pay data arrainged by a 'payperiod' date. This was fine for the first year of db operation but now we are looking at a new year and I was trying to get the child form to be able to display only the current year - not all the data. So I have been working on 'How to get a child form to display filtered data' e.g. the dates in the code above. IF possible I'd like to use bound columns in the child form so that the timekeeper can use the form to enter new data into each record (I got thinking over the weekend that if I use unbound fields, then I have to write MORE code for her to use the fields to input data - more and more complicated all the time)
Is there a simple way of selecting just the records in the child form for 2003 or 2004, etc? while keeping the old employee data in the master form.

 

[PHV]

What is the RecordSource of the subform ?
You may consider to base it on a query filtered to the current year.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

Again pokeymonsc, if I understand correctly, as PHV said, the filter you want, is not on the main form, but on the subform.
Secondly, yes I would make the subform bound, create a relationship on the proper fields.

remove completely, former procedure.

On the LOAD event of the MAIN FORM, you can put...

Me.subformName.Form.Recordsource = "SELECT * FROM subfrmTable WHERE payperiod like '#*/*/" & Year(Date) & "'"

As long as you've bound the subform & created the relationship, this should filter properly.

The user will not be able to view any records on the subform, outside of the current year, but will be able to read/write, add/delete on all records within.

Hope this helps, good luck!