a user opt in of field on a form

[datacontrol]

here is the situation.....

I have this field on a form that users key into and when the click apply, the record is stored in a table. Sometimes they will not need to use this particular field.

As it is now, if they leave it blank, an error will occur. The filed is a short date field, I have the table and the form input box formatted as such.

Basically, I want to have a radio button that turns this on/off and then the user can fill out the field if needed.

Code:

Option Compare Database
Option Explicit


Private Sub apply_Click()

If IsNull(Me!rr) Then
MsgBox "You must place values in all fields."

ElseIf IsNull(Me!rc) Then
MsgBox "You must place values in all fields."

ElseIf IsNull(Me!time) Then
MsgBox "You must place values in all fields."

Else

DoCmd.RunSQL "INSERT INTO tbldtc(tbldtc_date, tbldtc_user, tbldtc_num_req_rec, tbldtc_num_req_pro, tbldtc_time, tbldtc_oldest_date) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.old & "')"
DoCmd.Close
MsgBox "Your data has been saved."
Exit Sub
End If






End Sub



Private Sub Command24_Click()
DoCmd.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
SetFocus

End Sub

 

[Rich]

Why will an error occur, is the field set to required in the table?

 

[datacontrol]

......

Negative, the field is not set to required at the table level, but it does have short date formatting applied as well as the input box on the form. The input box also has an input mask specified. This is wher emy error is coming from.

The error I get is that blah blah blah......one fields is set to null due to a type conversion failure....

The interesting thing is that the table is being appended but the filed that that particular inoput box posts to is empty. This is what I want, actually but how to get rid of that error?

 

[Rich]

Why do you not just bind the form to the table/query?

 

[datacontrol]

bind?

bind? care to elaborate?

I am somewhat of a newbie/genius

 

[datacontrol]

still down

I seearched this forum for binding...no luck.

Will this really solve my problem?

I am open for suggestons

 

[Pat Hartman]

If you specify a table or query as the recordsource for a form or report, that form/report is "bound". Then you would choose a field from that recordsource as the controlsource for each control. Then the controls would be "bound". Access controls all the behind the scenes activity to manage the population of the controls and the updating of the bound record. You do not have to write code to do it yourself.

 

[VBITS]

Create a private function in the form as follows:
Sorry to use other variables (it's a copy paste )


Private Function IsFilled() as Boolean

Isfilled=True
If isnull(me.txtDate) then IsFilled = False
If isnull(me.txtName) then IsFilled = False
If isnull(me.txtContact) then IsFilled = False

End Function


Under the event of clicking your save button place:

Under each of the Textboxes event of After_Update use the next:


Private sub txtDate_AfterUpdate()
If isfilled = true then
me.cmdButton.enabled = true
Else
me.cmdButton.enabled = false
End if


Does this solve the problem?

 

[datacontrol]

thanks...looks good so far

This code looks like just what I need. I am having a bit of trouble with one part

Private Sub old_AfterUpdate()
If IsFilled = True Then
Me.cmdButton.Enabled = True
Else
Me.cmdButton.Enabled = False
End If

End Sub

Me.cmdbutton.Enabled.....

does the command button needs to be changed? Should I name it the same as the name of the field or the command button on my form?

I am getting an error here.

Thanks again!

 

[namliam]

Have you tried your original code with currentdb.execute instead of docmd.runsql?

docmd.setwarnings false
docmd.runsql "sql here"
docmd.setwarnings true

(or use currentdb.execute "Sql Here")

These shut up those nasty messages...

Regards

 

[datacontrol]

mission accomplished

I took a little advise from all of you and came up with this:

Private Sub apply_Click()

If IsNull(Me!rr) Then
MsgBox "You must place values in all fields."

ElseIf IsNull(Me!rc) Then
MsgBox "You must place values in all fields."

ElseIf IsNull(Me!time) Then
MsgBox "You must place values in all fields."

ElseIf IsNull(Me!old) Then
Me.old.Enabled = False
DoCmd.RunSQL "INSERT INTO tbldtc(tbldtc_date, tbldtc_user, tbldtc_num_req_rec, tbldtc_num_req_pro, tbldtc_time) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "')"
DoCmd.Close
MsgBox "Your data has been saved."

Else

DoCmd.RunSQL "INSERT INTO tbldtc(tbldtc_date, tbldtc_user, tbldtc_num_req_rec, tbldtc_num_req_pro, tbldtc_time, tbldtc_oldest_date) VALUES ('" & Me.date & "','" & Me.user & "','" & Me.rr & "','" & Me.rc & "','" & Me.time & "','" & Me.old & "')"
DoCmd.Close
MsgBox "Your data has been saved."
Exit Sub
End If






End Sub

Kinda weird actually, I added the extra else statement, then binded the from to the table. After I binded, everything worked fine except the fields that I directly related to table columns would have a default value of the last date entered instead of "0". So I unbinded the old field individaully along with another and presto! no errors.....weird huh?

Thanks again I hope this helps someone!

Now on to that pesky issue with dup records being added to my login table!

 

[VBITS]

With my code the 'apply' button comes available when everything is filled in, you need to make a button which is not enabled, and rename it to any desired name, and fill that in in stead of cmdButton