Help with clearing all fields and limiting to list...

[jgi]

Hey.

I've got a form that has a number of fields with lookup menus that I want to limit the user from entering new test into. I can easily do this with the Limit to List option. But I also have a button that clears all the fields. If there is some text in the locked field (that isn't in the lookup) and I try to clear the fields, it gives me the same error message it would were I trying to enter the text into that field. Subsequently it does not clear the fields. This also happens when I try to add a new record. Any insight would be greatly appreciated. Thanks.

jgi.

 

[WB786]

Can you post your coding for the clear button and the exact error message you are getting? Thanks.

WB

 

[luceze]

I do the same thing with this code:


Dim ctrl As Control
For Each ctrl In Me.Controls
With ctrl
Select Case .ControlType

Case acComboBox
ctrl.Value = ""
.Requery
End Select
End With

Next


This will clear all of your combo boxes and it did not produce any errors on my machine. You can change it to clear any type of control in your form if you need to.

HTH,
Eric

 

[jgi]

Eric.

Here is my coding, as per your suggestion:

Private Sub ClearAllFields_Click()
On Error GoTo Err_ClearAllFields_Click


Dim ctrl As Control
For Each ctrl In Me.Controls
With ctrl
Select Case .ControlType

Case acComboBox
ctrl.Value = ""
.Requery
Case acTextBox
ctrl.Value = ""
.Requery
Case acMemoBox
ctrl.Value = ""
.Requery
End Select
End With

Next

Exit_ClearAllFields_Click:
Exit Sub

Err_ClearAllFields_Click:
MsgBox Err.Description
Resume Exit_ClearAllFields_Click

End Sub

I, however, do get an error. The error says: "Field Competition.Title cannot be a zero length string." ... or something of the sort. "Competition" is the name of the database and "Title" is the name of one of the fields. Any insight?

Thanks.

jgi.

 

[luceze]

Is there any type of validation at the table or form level that requires a specified criteria for that field? Is the required property for that field set to "Yes"?

Eric

 

[WB786]

The only thing I can think of as luceze suggested as well is that your Title field property is set to Required and therefor it is requiring you to enter in a value and will not allow you set that field's value to Null.

WB

 

[RoyVidar]

If it's not required, then perhaps try toggle the allow zero length property of the field.

Roy-Vidar