Referencing a field with no record

[L'apprentis]

Is it possible to reference a field on a form via code that as no record available on the loading event of a form.
On the loading event of my form I have tried if TxtEssai="" then ...or if txtessai=null... but it tells me that I have entered an expression with no value which is making sense because the txtbox as no record on the loading event of the form. How should I write the beginning of my code to be able to use The "if" function on The field linked to txtEssai?

 

[Rich]

You could use the TimerEvent

 

[allan57]

You could reference the recordset to see if any records exist:

Private Sub Form_Load()

Dim rst As Recordset

Set rst = Me.RecordsetClone

If rst.RecordCount = 0 Then MsgBox "No Record"

End Sub

 

[L'apprentis]

cheers guys for your reply, I am not sure how to work the time event for this situation, I think I also understand the recordset method but I am non sure how to apply it to my situation the record that can be not available is inside a subform and I don't know if I got enough programming knowledge to do what your saying. To be more explicit, here is the entire code:

Private Sub Form_Load()
If Forms!FrmNewPattern!SFrmPattern2.Form.TxtSupplierName = Null Then
SFrmPattern2.Visible = False
Else
SFrmPattern2.Visible = True
End If
If Forms!FrmNewPattern!SfrmPattern.Form.TxtSupplierName = Null Then
SfrmPattern.Visible = False
Else
SfrmPattern.Visible = True
End If
End Sub

 

[allan57]

substitute Set rst = Me.RecordsetClone

with

Set rst = Form_TheNameOfSubFormHere.RecordsetClone

 

[L'apprentis]

Thanks again Allan 57, I have tried to use the syntax you gave me (Set rst = Form_TheNameOfSubFormHere.RecordsetClone) but it still doesn't work, I tried as well with an other syntax and it is not working, here is the code:

Private Sub Form_Load()
Dim rst1 As Recordset
Dim rst2 As Recordset
Set rst1 = Forms!FrmNewPattern!SfrmPattern.Form.RecordsetClone
Set rst2 = Forms!FrmNewPattern!SFrmPattern2.Form.RecordsetClone

If rst1.RecordCount = 0 Then
SfrmPattern.Visible = False
Else
SfrmPattern.Visible = True
End If

If rst2.RecordCount = 0 Then
SFrmPattern2.Visible = False
Else
SFrmPattern2.Visible = True
End If

End Sub

 

[allan57]

Try changing the following:-

Private Sub Form_Load()
Dim rst1 As Recordset
Dim rst2 As Recordset
Set rst1 = Form_SfrmPattern.RecordsetClone
Set rst2 = Form_SFrmPattern2.RecordsetClone

'note the usage
'Set rst = Form_TheNameOfSubFormHere.RecordsetClone

If rst1.RecordCount = 0 Then
SfrmPattern.Visible = False
Else
SfrmPattern.Visible = True
End If

If rst2.RecordCount = 0 Then
SFrmPattern2.Visible = False
Else
SFrmPattern2.Visible = True
End If

End Sub

 

[L'apprentis]

Thanks Allan, I still get a variable not defined error.

 

[allan57]

Check that your sub form properties 'Has Module'=yes

 

[L'apprentis]

Indeed 'Has Module' was set to no, i've changed it to yes and I get a msmatch error now.

 

[allan57]

L'apprentis

Could you step through your code and identify which line of code the error occurs.

Allan

 

[L'apprentis]

'Set rst1 = Form_SfrmPattern.RecordsetClone'

 

[allan57]

L'apprentis

Could you post a cut down version of your db in a 97 format

 

[L'apprentis]

I have copied all the Concerned form in a new database file: here is the file,

 

[allan57]

L'apprentis

Added reference 'Microsoft DAO 3.5 Library and I tried your cut down version and recieved the error you were getting, so I created I blank database and imported all your tables, forms etc. It now works. Attached is a copy of the database

View attachment db1.zip

 

[L'apprentis]

Thanks a lot Allan, It is working if I change some of the references in the library but now a different kind of problem which is kind of puzzling occurs.
To get the form to work I need to desactivate the "Microsoft Access activeXData Object 2.1 Library" but then, bits of code use with other objects don't work anymore:ie: the 2 command button situated on the "form Pattern". What I find really strange is that if I try to change back the library by adding "ActiveXData Object 2:1 library" back in the references, it doesn't do anything and the command button do not work anymore whereas they were working before??

 

[L'apprentis]

Solved...

I got the code to work using DAO

Dim rstCount1 As DAO.Recordset
Dim rstCount2 As DAO.Recordset

Set rstCount1 = Form_SfrmPattern.RecordsetClone
Set rstCount2 = Form_SfrmPattern2.RecordsetClone

If rstCount1.RecordCount = 0 Then
SfrmPattern.Visible = False
Else
SfrmPattern.Visible = True
End If

Allan57, thanks again for your help.