passing a hidden calculated field between forms

[crabback]

I have a form: frmScreen1 with a subform: subFrmScreen1. The the first form has a jobCode, with the subform holding details of versions of the job.
On the first form there is a button cmdNew that when clicked opens up frmScreen2 in a new record for the detail for a new version record. I want to populate the jobCode and Version fields in frmScreen2. The jobCode field is passed fine.
But for the Version field I have created a hidden textbox field on the subFrmScreen1, which counts the number of current versions and adds 1. This value is then passed to frmScreen2. It works fine when there is already +1 versions, but if there are no existing versions I get a 'runtime error: 2427 - You entered an expression that has no value'. So I tried trapping it in an if statement. but then I get a Compile error: type mismatch. I think this has something to do with the variable datatype, but I've tried it as a string and an integer - and get the same error.
I've also tried testing the variable in the if statement is null, is empty, = "".
This is wrecking my head! Please can anyone help me??
Thx in advance.
Here is my code:
***********************************************
Private Sub cmdNew_Click()
Dim strTl As String
Dim strCode As String
Dim iVno As Integer
iVno = SubfrmScreen1.Form!txTotV
If iVno Is Null Then
iVno = 1
Else
iVno = iVno
End If
strCode = Me.GenericJobCode
DoCmd.OpenForm "frmScreen2"
DoCmd.GoToRecord , , acNewRec
Forms!FrmScreen2!GenericJobCode = strCode
Forms!FrmScreen2!Version = iVno
DoCmd.Close acForm, "frmscreen1"

End Sub

 

[PHV]

Why not simply something like this ?
Private Sub cmdNew_Click()
DoCmd.OpenForm "frmScreen2"
DoCmd.GoToRecord , , acNewRec
Forms!FrmScreen2!GenericJobCode = Me!GenericJobCode
Forms!FrmScreen2!Version = Nz(Me!SubfrmScreen1.Form!txTotV, 1)
DoCmd.Close acForm, "frmscreen1"
End Sub

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

 

[crabback]

Thanks for the reply!
I tried that (much simpler code) but then I got an error msg saying 'You entered an expression that has no value' if there are no existing versions. The hidden text field value is read as null.
By the way, what is nz() that you used above. I've seen it but I don't know what it does?

 

[PHV]

Which line of code raised the error ?

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

 

[crabback]

Hi Phv
I've found that I had set in form design view the default value property of the hidden text field to 0 but I had also counted the number of versions by setting the control source to
= (Count(CInt([SubfrmScreen1.Form!Version]))) + 1
When I take this out the form behaves properly when creating the first version.
now I just need to know how to translate my sql '(Count(CInt([SubfrmScreen1.Form!Version]))) + 1' into vb.
thx for getting back to me!

Crabback

 

[PHV]

Perhaps the Nz and the DCount functions ?

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

 

[crabback]

I was thinking that. i haven't used the dcount. I'll come back.
thx

Crabback

 

[crabback]

PHV... when you say Nz do you mean something like...
Function NullToZero(anyValue As Variant) As Variant
' Accepts: a variant value
' Purpose: converts null values to zeros
' Returns: a zero or non-null value

If IsNull(anyValue) Then
NullToZero = 0
Else
NullToZero = anyValue
End If

End Function
?


Crabback

 

[PHV]

Why reinvent the wheel ?
In the debug window (Ctrl+G) type nz and then press the F1 key.

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

 

[crabback]

Hi
I've tried a few different approaches but I think should be right... its not, but can you tell me why?
inz = DCount(Me.SubfrmScreen1.Form!Version, Me.SubfrmScreen1)
iVno = Nz(inz) + 1 'SubfrmScreen1.Form!txTotV

I keep getting 'compile error: type mismatch'
any ideas?


Crabback

 

[crabback]

I finally got it to work.
There is no entry in the table the subform was built on is there is no existing version number.
I used some error handling code and it was very simple in the end. But I wonder is it just a quick fix, and have I created an even bigger problem down the road. What do you think?
This is the code I used:

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click

DoCmd.OpenForm "frmScreen2"
DoCmd.GoToRecord , , acNewRec
Forms!FrmScreen2!GenericJobCode = Me!GenericJobCode
Forms!FrmScreen2!Version = Me!SubfrmScreen1.Form!txTotV
DoCmd.Close acForm, "frmscreen1"

Exit_cmdNew_Click:
DoCmd.Close acForm, "frmscreen1"
Exit Sub

Err_cmdNew_Click:
Forms!FrmScreen2!Version = 1
Resume Exit_cmdNew_Click

End Sub


Crabback