Need help in vb coding

I have an access form, in the form I have a combo box, feild name is Master Agreement with yes and No, I have other feilds ACSadd_date, IRISadd_date, CD-ROMadd_date, Str Fund Mgr_add_date, Ediadd_date... and other feilds are like different services such as ACH, IRIS, CD-ROM..
So When a customer has couple of services such as ACH and IRIS, their corresponding add_date feild have a date. there are another fields are ACHcontract_reiceved, IRIScontract_reiceived, CD-ROMcontract_received..
What I want to do is if some body goes and change the Master Agreement combo box to yes and if the add_date feild is not null or empty then all the corresponding contract_received fields(which are combo boxes with a value list yes, no and not required) will be automatically yes.
Now I have the code that whenever sombody is clicking the master agreement combo box and saying yes, all the contract_received fields are becoming yes. How I modify my code so that I can accomodate another criteria that also ACHadd_date or IRISadd_date or CD-ROMadd_dateis not empty then only make the contract_received feild is "yes".
The code I have now is
Private Sub Master_Agreement_AfterUpdate()
If Me![Master Agreement] = "Yes" Then
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Name <> "Master Agreement" Then
ctl.Value = "Yes"
End If
End If
Next
End If
If Me![Master Agreement] = "No" Then
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Name <> "Master Agreement" Then
ctl.Value = "No"
End If
End If
Next
End If


End Sub

This is urgent. This project has to be finished by this wk. Any help will be appreciated.
Thanks.

Hi,

Your question is very confusing, but I think I get it. What you want:

When somebody sets the master agreement to yes, all the comboboxes for the services are set to yes only for those services that have a add_date.

With your current set-up, that's very hard. It will take alot of typing, but it is possible. First of, you need to use the following to check if a field is null (empty)



How did you name the comboboxes that contain the Yes/No values? If you could suply that, I might be able to give example code that does what you want within your loop.

Then, just a side question... You know how to normalize a database?

Grtz.©

M.

IRISadd_date (input mask is a date field) which correspond IRIScontract_received which is a combo box with a value list(no, yes, not required), default value "No"
other date fields are ACHadd_date correspond ACHcontract_received,
CD-ROMadd_date..........CD-ROMcontract_received.
EDIadd_date.................EDIcontract_received

You got the idea what exactly I wanted to do.
I will welcome your tips on normalization of a table.
Thanks for the help.

Can any body help me in this???????????

Hi,

This is a quicky for me, but I think this might do the trick.



Grtz.©

M.

Hi I tried the code but it is giving me the run time error
" Customer DB can't find the field 'sService & "add_date referred to in your expression" .
I put the code in the properties of Master Agreement after update event.
I need help on this .
Thanks.

Try this, I hope that it has a fields reference. Is this VB or VBA? From the looks of it VBA in a Access Form.

Grtz.©

It is VBA on a forms.
I tried the code it is giving me another error "compile error- Method or data member not found" It is pointing the Fields in the code.

I need help on this!
Thanks.

Substitute me.fields("x") for the recordset that you update with the data.

unfortunately, It is not working

Dim sService As String

If Me![Master Agreement] = "Yes" Then
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Name <> "Master Agreement" Then
sService = Left$(ctl.Name, Len(ctl.Name) - 17)
sService = sService & "add_date"
If (Not IsNull(Me.Fields(sService).Value)) Then
ctl.Value = "Yes"
End If
End If
End If
Next
End If

I replaced Me!Fields with IRISadd_date, it is not working,
I replaced with only IRIS, that's not working either...
When I am selecting Master Agreement to Yes all the combo contract_received are set to yes automatically, which I don't want. I only want to set IRIScontract_received to yes because IRISadd_date is has a date(not null).
I need help on this.
Thanks.

Is there any thought in it? I am stuck!!
Thanks

try stepping through your code with the debugger. You should be able to pinpoint the error that way.

Hi,

I seem to fail in this question, my sincere apologies for that. From my point of view, it is easy enough to see that you need to access the fields of the recordset on the form or the datacontrol that is coupled to the controls. However, I get the distinct feeling that those concepts are not understood.

Let me ask this the other way round. Do you have a datacontrol on your form? If so, what is the name of it. If you have no datacontrol on your form, how do you retrieve the data in the controls?

Grtz.©

M.

P.s. If I step on someone's toes, I am sorry. The above is my professional opinion.