Assigning text values, use Form_Current()?

[Junkee Brewster]

Hello again,

I'm having some problems calculating dates with VB on my subform. Here's the code I'm using (attached to the SubFrm)

Private Sub Form_Current()
If (Not IsNull(Me.LastTest)) Then
    
    If Me.txtTestPeriod = 1 Then
        Me.ReTest = DateAdd("yyyy", 1, Me.LastTest)
        End If
    If Me.txtTestPeriod = 3 Then
        Me.ReTest = DateAdd("yyyy", 3, Me.LastTest)
        End If   
   (etc etc.....)

This only seems to assign the date to the field in the FIRST row of the Subform. If I have multiple entries in the subform, then the date will only appear in row two onwards if I click on the date field(s) with my cursor.

Is it a coding error, or should I be putting the code NOT as Form_Current()?

Also, If I finish off the code after all my DateAdds with:

Else
Me.ReTest = " "
End If
End Sub

I get errors. I'm not sure if that part is needed anyways.. but it will work if I'm running off a Main Form only, but it seems the MainFrm/SubForm setup causes probs. I can't refer to it as Forms!SubFrmStuff!ReTest either as it says MSA can't find it.

Thanks again.

 

[RuralGuy]

Only one record will be "Current" in a SubForm at a time. It is generally the one at the top, unless you select a different one.

As to referring to the SubForm from the main form the syntax is:
Me!SubFormControlName.Form!ControlName. Here's a good reference on the subject.

 

[Junkee Brewster]

Cheers RG,

And thanks for the link - very handy indeed!

Is there any way for me to fix it so it will autofill all the fields in the subform?

 

[RuralGuy]

Are all of the controls mentioned in the subform bound to fields in the underlying query/table? How is txtTestPeriod set?

 

[Rich]

Scrap the vba and use a calculated control

 

[Junkee Brewster]

RG,

The fields LastTest and ReTest are both in the underlying Query. The user inputs data in LastTest, but VB will (hopefully) update ReTest.

txtTestPeriod is problematic - I've got it set on the subform by referring to the main form: =Forms!MainFrm!TestPeriod. I cannot seem to get the TestPeriod field in the query for the sub.

The reason for this is how it's set up. The main form table contains a summary of tool types eg. "Drill" "Chainsaw" etc, and has a corresponding TestPeriod for each type of tool. The subform then shows the total amount of drills etc - so there are multiple listings of "Drill", I cannot link TestPeriod to the subform query without it becoming nonsensical (that I can work out anyways). Nor do I want the user to input a test period everytime in the subform because that breeds errors.

Rich,

I'm unsure how to include so many conditions into the one textbox/field. Would that be nested IIf() statements? I'm not too crash hot at big complex ones...

Thanks again for any help - I'm really only a beginner with VB so I'm not always clear if things should be in Current() or After Update or a Module etc

 

[RuralGuy]

Hi Rich,
You sort of lost me on that last post. If it helps, remember the SubForm is based on a query/table that is accessable directly if you need to alter a field. You then could requery the SubForm and display the recent data.

 

[Rich]

How many conditions are there?

 

[Junkee Brewster]

Rich,

There are 7 different TestPeriods (years) 0, 0.5, 1, 2, 3, 5, 10, stored corresponding to the tool type, on TblToolGroups, displayed on Main Form, FrmTools.

On the subform (TblTools) there is only one field "ReTest" that needs to hold the calculated dates contingent on the different TestPeriods, which would be OK IF I could assign the TestPeriod data from TblToolGroups (main form), to TblTools (and thus the sub), then I could use a nested IIf()??

The only way I can currently assign TestPeriod to the subform is by using a text box with =Forms!FrmTools!TestPeriod, because of the way my tables are set up. Here's why:

TblToolGroups (this is the table on the MAIN form, FrmTools):

ToolType TestPeriod
Drill - 1
MultiMeter - 1
FireExt - 3
Chainsaw - 1
Gloves - 0.5
Tester - 5
(so there is only ever 1 entry for any given tool)

TblTools (this is the table on the SUBFORM, which will display mulitple values of the same tool type)

ToolType Data... (and NO TEST PERIOD)
Drill
Drill
Drill
Gloves
Drill


So you see, I cannot join the two in a query, because if I do, it becomes nonsensical (ie. I cannot add new records). If I try with my (limited) SQL, then the query corrupts (what mostly happens is the resulting query stuffs up TblToolGroups, and displays multiple entries for the same tool group, in order to match with TblTools.

I only ever want "TestPeriod" to be entered ONCE in relation to the Type of tool, to eliminate errors, so, that is why you don't manually enter it in on the subform over and over again.

So to summarise, If I can get TestPeriod onto the underlying query of the subform, then could you give me an idea how to do the nested IIf()? or expression?

Otherwise I'm going to have to still work it out with VB I think (which only half works at this stage!) Thanks again