Combo box programming question 1

[nwalk81]

I actually have two questions:

1. How would I program/format a combo box to be null/blank until the user actually chooses an entry. Right now the box automatically choices it's default answer, but I would like default to be blank.

My second issue:

I have a combo box with simple "Yes" or No" choices. I need the combo box to automatically display "No" if an employee has been working for less than a year.

Here is what I thought would generate the auto "No"
however it does not

Private Sub cmbFML_BeforeUpdate(Cancel As Integer)
If DateDiff(d, ServiceDt, Now) < 365 Then
Me.cmbFML = "No"
Else
Me.cmbFML IsNull
End If
End Sub

Can someone please tell me where I'm going wrong.

Thanks in advance

 

[rjoubert]

Try Me.ComboName.Value = "" to blank out your combo box. In your code for your 2nd issue, try Me.cmbFML.Value="No" and Me.cmbFML.Value = "".

 

[nwalk81]

rjoubert,

I tried your suggestion, however I still get the same result. Am I placing the code in the wrong location? Right now I have the code as a Before Update event. Is that appropiate or is there a better location?

Thanks for you assistance.

 

[rjoubert]

I would think you could put that in the Form Current event. It doesn't make sense to me where you have it now. The BeforeUpdate event of your combo box would fire whenever the value is changed.

 

[nwalk81]

When I place the code as an event on Form Current I get an invaild procedure call or argument message

 

[rjoubert]

So your code looks like this?

Private Sub Form_Current()
   If DateDiff(d, ServiceDt, Now) < 365 Then
      Me.cmbFML.Value = "No"
   Else
      Me.cmbFML.Value = ""
   End If
End Sub

 

[rjoubert]

Sorry I missed it...You need quotes around the d in your DateDiff call...and it may be a good idea to add .Value after your ServiceDt field...

DateDiff("d", ServiceDt.Value, Now)

 

[rjoubert]

I just created a form with a servicedt field and cmbFML combo box, and put the following code in the Current event of the Form, and it worked...

Private Sub Form_Current()
   If DateDiff("d", servicedt.Value, Now) < 365 Then
      Me.cmbFML.Value = "No"
   Else
      Me.cmbFML.Value = ""
   End If
End Sub

Good Luck!

 

[nwalk81]

rjoubert,

Thank you for all of your assistance, I've realized why I'm getting errors. ServiceDt is a field on the main form and cmbFML is a field in one of the sub-forms on the main form. So, would I have to code the main form instead of the sub-form and how can I pass the main form value to the sub-form.

I hope this is clear. Thanks again for being patient with me.

 

[rjoubert]

Replace subform with the actual name of your subform control...

Private Sub Form_Current()
   If DateDiff("d", servicedt.Value, Now) < 365 Then
      Me.subform.controls("cmbFML").Value = "No"
   Else
      Me.subform.controls("cmbFML").Value = ""
   End If
End Sub

 

[nwalk81]

I placed the code above as an event on Form current (in subform) however for some strange reason I'm getting a field referred to in expression can't be found error.

I even tried to add a blank space entry to the combo box selections to no avail.

Have any idea why this is happening? Should I place code in main form?

Thx

 

[rjoubert]

Yes, the code I posted should be in the Main Form's Current event.

 

[nwalk81]

Thank you so much for your help. I had the table settings on yes/no instead of text. Everything is working beautifully.

Thx