Sub Form specific values

  • Thread starter Thread starter Dan @BCBS
  • Start date Start date
D

Dan @BCBS

My Objective:
User enters data in a field on a form.
The form has a subform.

When data is entered, only 2 choices appear on the combo box, on the subform.
And the user cannot close the form unless one is picked.

Here is what I have so far, but I'm stick on the last line, etc.
If Me.TR_ACKNOWLTR <> " " Then
If MsgBox("You pick from combo box. Continue? ", vbQuestion & vbYesNo,
"Question") = vbYes Then

Me!f_CaseLog!CA_NAME = "AG" or "AL"
(Need only AG or AL to appear in combo box)
(Need to not allow form to be closed until one is picked).

Help!
 
Dan,
Set up the row source for the combo on the combo's property dialog.
On the Data tab, set the Row Source Type to Value List
In the Row Source type the following:
"AG";"AL"

If the Before update event for the subform put code like this:

If IsNull(Me.TheCombo) Then
Msgbox "You must choose the group/type/category"
Cancel = True
End If

Replace TheCombo with the name of your combo and group/type/category with
whatever fits..

Jeanette Cunningham
 
I have a simular problem but it is a list box in a sub form. When a user trys
to close the parent form I want to check if list box "Client" was answered
first. But the forms just close??


Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Client) Then
MsgBox "Select Client before you can close"
Cancel = True

End If
End Sub
 
Arlend,
the code below:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Client) Then
MsgBox "Select Client before you can close"
Cancel = True

End If
End Sub
Click to expand...

needs to go on the subform's before update event, not the parent form's
before update event.
I assume Client is the name of the Listbox control.
Make sure you select the subform, then the subform's before update event and
make sure the code is there.

The way access works - when you move your cursor out of the subform and onto
the parent form, it triggers an automatic save of the data on the subform.

Jeanette Cunningham
 
Yes this works. But here is my problem, the subform is not always clicked on.
Is there a way to do this check when the subform is never touched?
 
It's a bit unusual - users can usually add a new parent record without
adding a child record. Users can usually add several child records to the
same parent record.
Tell us more about your parent form and subform. What data is being entered,
what is the linking field, why would you want the user to always fill in the
client on the subform?

Jeanette Cunningham
 
The parent form is the users main viewing place of lots of diff. data from
diff. tables the sub form im talking about has four questions that would be
updated if they did work on that customer. If no work was done then it would
be left blank. Im trying to create a msgbox as a reminder to update client if
work was done.

hope this makes sense.
 
Yes, I think I understand what you want to do.
I assume there is no parent - child relationship between the data in the
main form and the subform.
Users need the option to update client or not.
I don't have any good solutions. - Don't know if this will work, I haven't
tried it. Suggestion is put an option group on the main form asking if work
was done for the client shown on the main form. Make it with 2 choices yes
or no. Make it compulsory to fill in. In the code for the close button on
the main form, have code that checks the client details table to see if
there is data for that particular client when work was done - if not, pop
up a message and keep the form open. Users could change the option group to
no, to avoid filling in the client details. I don't really know of any
system to force users to keep a database up to date with all data.

Jeanette Cunningham
 
Don't beleive I can do it that way:
1. The combo was created from ta table so the row source already has a value.
2. the combo box has other code associated with it in the "On Change"

That is why I have, vbQuestion if Yes then only AG or AL should appear in
the combo box. This is were I'm stuck!!

Me!f_CaseLog!CA_NAME = "AG" or "AL"
 
Thanks form all your help Jeanette'

Arlend

Jeanette Cunningham said:
Yes, I think I understand what you want to do.
I assume there is no parent - child relationship between the data in the
main form and the subform.
Users need the option to update client or not.
I don't have any good solutions. - Don't know if this will work, I haven't
tried it. Suggestion is put an option group on the main form asking if work
was done for the client shown on the main form. Make it with 2 choices yes
or no. Make it compulsory to fill in. In the code for the close button on
the main form, have code that checks the client details table to see if
there is data for that particular client when work was done - if not, pop
up a message and keep the form open. Users could change the option group to
no, to avoid filling in the client details. I don't really know of any
system to force users to keep a database up to date with all data.

Jeanette Cunningham
Click to expand...
 
Back
Top