Fields to be Filled in

G

Guest

Hi

I have a field called "Carer Availability". This field is a combo box and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value = 2).
They return these values to a table

After this field i have several fields relating to Carer details (Carer
Family Name, Carer Given Name, Carer DOB and Carer Language) all of this
fields need to filled in if the Carer availability = has a carer (Value = 2)
If they are not all filled in then i need a message box advising which ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB code.

Thanks

Andrew
 
A

Allen Browne

Hi Andrew.

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of the
items and subitems as fields in your table, you could use the BeforeUpdate
event procedure of your *form* (not text box), to test if the fields are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different data
structure, depending on what else you need to do.
 
G

Guest

Hi Allen

Sounds like you have heard of these reports......

I shall explain a little further to my setup up. I have a clients active
form, data for that form comes from the clients table. On that form i have a
tab control object with a TAB for each of the different pages of an ONI
documentation. Each page is set up with a subform and has its own source
table.

In the Form Header i have command buttons, one called edit Info. I have all
the properties for the TAB's set to "enabled = no" so people cannot change
anything. When they click the edit info it changes the "enabled = yes" so
they can edit it.

Anyhow i have put the code on the subform but nothing happens when i enter
the form. Here is a copy of the code i have got so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Available] = 1 Then
If IsNull(Me.[Carer Family Name] Or _
IsNull(Me.[Carer Given Name])) Then
Cancel = True
MsgBox "More carer details needed."
End If
End If
End Sub

They have to click a Save button which changes the "enabled = yes" back to a
"no" is is possible to add the code in to check for fields that need to be
filled in.

Hope you can help

Thanks

Andrew



Allen Browne said:
Hi Andrew.

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of the
items and subitems as fields in your table, you could use the BeforeUpdate
event procedure of your *form* (not text box), to test if the fields are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different data
structure, depending on what else you need to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Hi

I have a field called "Carer Availability". This field is a combo box
and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value = 2).
They return these values to a table

After this field i have several fields relating to Carer details (Carer
Family Name, Carer Given Name, Carer DOB and Carer Language) all of this
fields need to filled in if the Carer availability = has a carer (Value =
2)
If they are not all filled in then i need a message box advising which
ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB code.

Thanks

Andrew
Click to expand...
Click to expand...
 
A

Allen Browne

Do these tabs contains subforms?
Or do the different tabs just contain controls bound to the main form's
table?

If there are subforms, you need to use the BeforeUpate of each subform.
If they are all bound to the main form's table, the code you have should
work.

Your save button: what does it do?
I imagine it would be something like this:
If Me.Dirty Then Me.Dirty = False
That would trigger the checks in Form_BeforeUpdate, which would cancel the
save if either [Carer Family Name] or [Carer Given Name] is null, and it
would report that you can't set the Dirty property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Hi Allen

Sounds like you have heard of these reports......

I shall explain a little further to my setup up. I have a clients active
form, data for that form comes from the clients table. On that form i
have a
tab control object with a TAB for each of the different pages of an ONI
documentation. Each page is set up with a subform and has its own source
table.

In the Form Header i have command buttons, one called edit Info. I have
all
the properties for the TAB's set to "enabled = no" so people cannot
change
anything. When they click the edit info it changes the "enabled = yes" so
they can edit it.

Anyhow i have put the code on the subform but nothing happens when i enter
the form. Here is a copy of the code i have got so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Available] = 1 Then
If IsNull(Me.[Carer Family Name] Or _
IsNull(Me.[Carer Given Name])) Then
Cancel = True
MsgBox "More carer details needed."
End If
End If
End Sub

They have to click a Save button which changes the "enabled = yes" back to
a
"no" is is possible to add the code in to check for fields that need to
be
filled in.

Hope you can help

Thanks

Andrew



Allen Browne said:
Hi Andrew.

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of the
items and subitems as fields in your table, you could use the
BeforeUpdate
event procedure of your *form* (not text box), to test if the fields are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different data
structure, depending on what else you need to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Hi

I have a field called "Carer Availability". This field is a combo box
and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value =
2).
They return these values to a table

After this field i have several fields relating to Carer details (Carer
Family Name, Carer Given Name, Carer DOB and Carer Language) all of
this
fields need to filled in if the Carer availability = has a carer (Value
=
2)
If they are not all filled in then i need a message box advising which
ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB
code.

Thanks

Andrew
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry for the delay in replying.

Each tab has its own sub-form. Have tried putting the code in on the
subform but still does nothing.

Here is the code i have for my save button. Im not familiar with the
me.dirty command can you explain it a little more

Private Sub Save_Click()
On Error GoTo Err_Save_Click
If Me.Deceased = True Then
'Client ceased using your services
Me.Active = "No"
Me.DDate.Enabled = False
Me.DDate.Locked = True
Me.Cessation_Reason.Enabled = False
Me.Cessation_Reason.Locked = True
Else
'Client still uses your services
Me.DDate.Enabled = False
Me.Cessation_Reason.Enabled = False
Me.DDate.Locked = False
Me.Cessation_Reason.Locked = False
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.id.SetFocus
Me.Edit.Visible = True
Me.save.Visible = False
Me.Menu.Visible = True
Me.General.Enabled = False
Me.Core.Enabled = False
Me.Core_2.Enabled = False
Me.Core_3.Enabled = False
Me.Functional.Enabled = False
Me.Living_Arrangements.Enabled = False
Me.Carer.Enabled = False
Me.Health_Conditions.Enabled = False
Me.Health_Conditions_2.Enabled = False
Me.Psychosocial.Enabled = False
Me.Health_Behaviours.Enabled = False
Me.Label97.Visible = False
Me.Search.Enabled = True

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

Thanks

Andrew


Allen Browne said:
Do these tabs contains subforms?
Or do the different tabs just contain controls bound to the main form's
table?

If there are subforms, you need to use the BeforeUpate of each subform.
If they are all bound to the main form's table, the code you have should
work.

Your save button: what does it do?
I imagine it would be something like this:
If Me.Dirty Then Me.Dirty = False
That would trigger the checks in Form_BeforeUpdate, which would cancel the
save if either [Carer Family Name] or [Carer Given Name] is null, and it
would report that you can't set the Dirty property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Hi Allen

Sounds like you have heard of these reports......

I shall explain a little further to my setup up. I have a clients active
form, data for that form comes from the clients table. On that form i
have a
tab control object with a TAB for each of the different pages of an ONI
documentation. Each page is set up with a subform and has its own source
table.

In the Form Header i have command buttons, one called edit Info. I have
all
the properties for the TAB's set to "enabled = no" so people cannot
change
anything. When they click the edit info it changes the "enabled = yes" so
they can edit it.

Anyhow i have put the code on the subform but nothing happens when i enter
the form. Here is a copy of the code i have got so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Available] = 1 Then
If IsNull(Me.[Carer Family Name] Or _
IsNull(Me.[Carer Given Name])) Then
Cancel = True
MsgBox "More carer details needed."
End If
End If
End Sub

They have to click a Save button which changes the "enabled = yes" back to
a
"no" is is possible to add the code in to check for fields that need to
be
filled in.

Hope you can help

Thanks

Andrew



Allen Browne said:
Hi Andrew.

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of the
items and subitems as fields in your table, you could use the
BeforeUpdate
event procedure of your *form* (not text box), to test if the fields are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different data
structure, depending on what else you need to do.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi

I have a field called "Carer Availability". This field is a combo box
and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value =
2).
They return these values to a table

After this field i have several fields relating to Carer details (Carer
Family Name, Carer Given Name, Carer DOB and Carer Language) all of
this
fields need to filled in if the Carer availability = has a carer (Value
=
2)
If they are not all filled in then i need a message box advising which
ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB
code.

Thanks

Andrew
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, each tab contains a subform. Each subform will have its own
Form_BeforeUpdate event.

Access saves the record in one subform before you move focus back to the
main form, or to another subform. Therefore you must use the BeforeUpdate
event procedure of each (sub)form in order to check or modify that record
before it is saved.

See help on the Dirty property. It's a way of referring explicitly to the
form whose record needs to be saved, regardless of whether that form has
focus or not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Sorry for the delay in replying.

Each tab has its own sub-form. Have tried putting the code in on the
subform but still does nothing.

Here is the code i have for my save button. Im not familiar with the
me.dirty command can you explain it a little more

Private Sub Save_Click()
On Error GoTo Err_Save_Click
If Me.Deceased = True Then
'Client ceased using your services
Me.Active = "No"
Me.DDate.Enabled = False
Me.DDate.Locked = True
Me.Cessation_Reason.Enabled = False
Me.Cessation_Reason.Locked = True
Else
'Client still uses your services
Me.DDate.Enabled = False
Me.Cessation_Reason.Enabled = False
Me.DDate.Locked = False
Me.Cessation_Reason.Locked = False
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.id.SetFocus
Me.Edit.Visible = True
Me.save.Visible = False
Me.Menu.Visible = True
Me.General.Enabled = False
Me.Core.Enabled = False
Me.Core_2.Enabled = False
Me.Core_3.Enabled = False
Me.Functional.Enabled = False
Me.Living_Arrangements.Enabled = False
Me.Carer.Enabled = False
Me.Health_Conditions.Enabled = False
Me.Health_Conditions_2.Enabled = False
Me.Psychosocial.Enabled = False
Me.Health_Behaviours.Enabled = False
Me.Label97.Visible = False
Me.Search.Enabled = True

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

Thanks

Andrew


Allen Browne said:
Do these tabs contains subforms?
Or do the different tabs just contain controls bound to the main form's
table?

If there are subforms, you need to use the BeforeUpate of each subform.
If they are all bound to the main form's table, the code you have should
work.

Your save button: what does it do?
I imagine it would be something like this:
If Me.Dirty Then Me.Dirty = False
That would trigger the checks in Form_BeforeUpdate, which would cancel
the
save if either [Carer Family Name] or [Carer Given Name] is null, and it
would report that you can't set the Dirty property.

Andrew C said:
Hi Allen

Sounds like you have heard of these reports......

I shall explain a little further to my setup up. I have a clients
active
form, data for that form comes from the clients table. On that form i
have a
tab control object with a TAB for each of the different pages of an ONI
documentation. Each page is set up with a subform and has its own
source
table.

In the Form Header i have command buttons, one called edit Info. I
have
all
the properties for the TAB's set to "enabled = no" so people cannot
change
anything. When they click the edit info it changes the "enabled = yes"
so
they can edit it.

Anyhow i have put the code on the subform but nothing happens when i
enter
the form. Here is a copy of the code i have got so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Available] = 1 Then
If IsNull(Me.[Carer Family Name] Or _
IsNull(Me.[Carer Given Name])) Then
Cancel = True
MsgBox "More carer details needed."
End If
End If
End Sub

They have to click a Save button which changes the "enabled = yes" back
to
a
"no" is is possible to add the code in to check for fields that need
to
be
filled in.

:

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of
the
items and subitems as fields in your table, you could use the
BeforeUpdate
event procedure of your *form* (not text box), to test if the fields
are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different
data
structure, depending on what else you need to do.


I have a field called "Carer Availability". This field is a combo
box
and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value =
2). They return these values to a table

After this field i have several fields relating to Carer details
(Carer
Family Name, Carer Given Name, Carer DOB and Carer Language)
all of this
fields need to filled in if the Carer availability = has a carer
(Value
= 2)
If they are not all filled in then i need a message box advising
which
ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB
code.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen

Finally got it to work on the carers subform but having problems getting it
to work on another subform.


Here is the code i have on the before update event
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.[Surname]) Or IsNull(Me.[First]) Then
Cancel = True
MsgBox "More Details needed."
End If
Me.HACC_Linkage = Me.HACCcode
End Sub

Also is there any restrictions to how many isnull statements you can use??

Thanks

Andrew


Allen Browne said:
Okay, each tab contains a subform. Each subform will have its own
Form_BeforeUpdate event.

Access saves the record in one subform before you move focus back to the
main form, or to another subform. Therefore you must use the BeforeUpdate
event procedure of each (sub)form in order to check or modify that record
before it is saved.

See help on the Dirty property. It's a way of referring explicitly to the
form whose record needs to be saved, regardless of whether that form has
focus or not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Andrew C said:
Sorry for the delay in replying.

Each tab has its own sub-form. Have tried putting the code in on the
subform but still does nothing.

Here is the code i have for my save button. Im not familiar with the
me.dirty command can you explain it a little more

Private Sub Save_Click()
On Error GoTo Err_Save_Click
If Me.Deceased = True Then
'Client ceased using your services
Me.Active = "No"
Me.DDate.Enabled = False
Me.DDate.Locked = True
Me.Cessation_Reason.Enabled = False
Me.Cessation_Reason.Locked = True
Else
'Client still uses your services
Me.DDate.Enabled = False
Me.Cessation_Reason.Enabled = False
Me.DDate.Locked = False
Me.Cessation_Reason.Locked = False
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.id.SetFocus
Me.Edit.Visible = True
Me.save.Visible = False
Me.Menu.Visible = True
Me.General.Enabled = False
Me.Core.Enabled = False
Me.Core_2.Enabled = False
Me.Core_3.Enabled = False
Me.Functional.Enabled = False
Me.Living_Arrangements.Enabled = False
Me.Carer.Enabled = False
Me.Health_Conditions.Enabled = False
Me.Health_Conditions_2.Enabled = False
Me.Psychosocial.Enabled = False
Me.Health_Behaviours.Enabled = False
Me.Label97.Visible = False
Me.Search.Enabled = True

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

Thanks

Andrew


Allen Browne said:
Do these tabs contains subforms?
Or do the different tabs just contain controls bound to the main form's
table?

If there are subforms, you need to use the BeforeUpate of each subform.
If they are all bound to the main form's table, the code you have should
work.

Your save button: what does it do?
I imagine it would be something like this:
If Me.Dirty Then Me.Dirty = False
That would trigger the checks in Form_BeforeUpdate, which would cancel
the
save if either [Carer Family Name] or [Carer Given Name] is null, and it
would report that you can't set the Dirty property.

Hi Allen

Sounds like you have heard of these reports......

I shall explain a little further to my setup up. I have a clients
active
form, data for that form comes from the clients table. On that form i
have a
tab control object with a TAB for each of the different pages of an ONI
documentation. Each page is set up with a subform and has its own
source
table.

In the Form Header i have command buttons, one called edit Info. I
have
all
the properties for the TAB's set to "enabled = no" so people cannot
change
anything. When they click the edit info it changes the "enabled = yes"
so
they can edit it.

Anyhow i have put the code on the subform but nothing happens when i
enter
the form. Here is a copy of the code i have got so far

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Available] = 1 Then
If IsNull(Me.[Carer Family Name] Or _
IsNull(Me.[Carer Given Name])) Then
Cancel = True
MsgBox "More carer details needed."
End If
End If
End Sub

They have to click a Save button which changes the "enabled = yes" back
to
a
"no" is is possible to add the code in to check for fields that need
to
be
filled in.

:

Sounds like a government report? HACC perhaps?

The answer will depend on your data structure. If you have all 71 of
the
items and subitems as fields in your table, you could use the
BeforeUpdate
event procedure of your *form* (not text box), to test if the fields
are
null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If Me.[Carer Availability] = 1 Then
If IsNull(Me.[Carer Family Name] OR _
IsNull(Me.[Carer Given Name]) OR ... Then
Cancel = true
MsgBox "More carer details needed."
End If
End If
End Sub

But in reality, we use you might be better served with a different
data
structure, depending on what else you need to do.


I have a field called "Carer Availability". This field is a combo
box
and
displays either "Has a carer" (Value = 1) or "Has No Carer" (Value =
2). They return these values to a table

After this field i have several fields relating to Carer details
(Carer
Family Name, Carer Given Name, Carer DOB and Carer Language)
all of this
fields need to filled in if the Carer availability = has a carer
(Value
= 2)
If they are not all filled in then i need a message box advising
which
ones
need to be filled in.

Can someone help me out with this. Im only starting to learning VB
code.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

That's perfectly valid.

You might want to check that the form's BeforeUpdate property is set to:
[Event Procedure]
Perhaps the property setting got lost.

You could also add a Stop at the top of the code, so that Access stops when
the code is run. You can then press F8 to single-step through the code to
see what it's up to.

You might also like to open the table in design view, and make sure the
Allow Zero Length property is set to No for both fields. That will prevent
it saving a record that contains a zero-length string (which is not the same
as Null.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto Fill 1
Auto fill field in subform (like Unit Price) 1
Auto-Fill Text box on form 4
Great difficulty solving a would be easy problem 3
Date last filled... 18
stop automatic data filling in combo boxes 5
Does it work with a loop 1
compulsary field 2

Top