Link a checkbox on my form to another table

[MsLady]

This form is bound to another table. Now i want to include a checkbox on this form that will be bound to an entirely different (small) table. How do i do this?

 

[RuralGuy]

Are the two tables related? Can you put them both in the same query?

 

[MsLady]

Are the two tables related? Can you put them both in the same query?

Hmn..no. I take this back. they are not related.
Some user are supposed to enter info on my form everyweek. If they don't have any info for that week. They will go in and check the non-applicable checkbox. I want this info saved into another tiny table "tblNonapplicable". but not on the main table.

Main table is a major table which this form is bound to. then, i just created a checkbox on the form. So that if that "non-applicable" checkbox is checked. Data is placed in this new tiny table "tblNonapplicable" (with just two fields, ID, firstname and lastname).

 

[RuralGuy]

It actually sounds like your data is not completely normalized. Wouldn't adding a True/False field to the main table with it being checked indicating this Name/ID would be in the other table be just as effective? You could easily add a record to that other table when you check the CheckBox but are you prepared to delete the record when the user UnChecks the CheckBox?

 

[MsLady]

Thansk for your prompt response

Its' a good idea but i am a little reluctant to add any more fields to the main table. it's a huge table with sybase backend. And alot objects are depending on it and vice versa.

I was just hoping i can add records only to that tiny "tblNonapplicable" table if that checkbox is checked...Isn't it possible? not wise?

 

[MsLady]

Another reason i don't want to add any more fields to the major table:

I am trying not to create extra work for myself, in which i will have to go and start modifying the gazillion queries and who knows how many objects we have depending on this table. Every row is calculated. and if i include the checkbox, and when the user checks it. it's gonna create a record for that. And the data in the record will be empty...expect the checkbox. This is still gonna show up on reports and other things...u know.

The non-applicable users have simply not be putting data up till now. But we want to differentiate the knuckle heads that refuse to put in thier weekly data from the ones that are simply Non-applicable (this is where the tblNonapplicable should come in --- getiting it's data from the checkbox).

 

[MsLady]

You could easily add a record to that other table when you check the CheckBox but are you prepared to delete the record when the user UnChecks the CheckBox?

yes, i wanna delete when he unchecks too
Please tell me how
But just to add data into that tblNon-applicable table alone.

 

[RuralGuy]

Your CheckBox click event code will look something like this:

Private Sub ChkBox_Click()
On Error GoTo Err_ChkBox_Click
Dim MySQL As String

If Me.chkActive Then
   '-- CheckBox going to "Checked" state
   MySQL = "Insert Into YourTableName(ID,FullName) " & _
        "Values(" & Me.IDControl & ", """ & Me.FullNameControl & """)"
Else
   '-- CheckBox going to "UnChecked" state
   MySQL = "DELETE * FROM YourTableName WHERE [ID] = " & Me.IDControl
End If

CurrentDb().Execute MySQL, dbFailOnError

Exit_ChkBox_Click:
   Exit Sub

Err_ChkBox_Click:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_ChkBox_Click

End Sub

You will of course need to use your control names and keep in mind it is <<air code>> so it might have errors. It should get you started.

 

[MsLady]

Thank you soo much for taht code *muah
Here is what i have now

Private Sub chkNoSupportHrs_Click()
On Error GoTo Err_ChkBox_Click
Dim MySQL As String

If Me.ActiveControl Then
   '-- CheckBox going to "Checked" state
   MySQL = "Insert Into tblNoSupportHrs(ShortID,WeekEnding) " & _
        "Values(" & Me.txtResource & ", """ & Me.Text18 & """)"
Else
   '-- CheckBox going to "UnChecked" state
   MySQL = "DELETE * FROM tblNoSupportHrs WHERE [ShortID] = " & Me.txtResource
End If

CurrentDb().Execute MySQL, dbFailOnError

Exit_ChkBox_Click:
   Exit Sub

Err_ChkBox_Click:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_ChkBox_Click

End Sub

However i get the error: 3061. Too few parameter. expected 1.
What do you think?


.

 

[RuralGuy]

Here's a code change:

Private Sub chkNoSupportHrs_Click()
On Error GoTo Err_ChkBox_Click
Dim MySQL As String

If Me.[b]chkNoSupportHrs[/b] Then
   '-- CheckBox going to "Checked" state
   MySQL = "Insert Into tblNoSupportHrs(ShortID,WeekEnding) " & _
        "Values(" & Me.txtResource & ", """ & Me.Text18 & """)"
Else
   '-- CheckBox going to "UnChecked" state
   MySQL = "DELETE * FROM tblNoSupportHrs WHERE [ShortID] = " & Me.txtResource
End If

[b]MsgBox MySql[/b]
CurrentDb().Execute MySQL, dbFailOnError

Exit_ChkBox_Click:
   Exit Sub

Err_ChkBox_Click:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_ChkBox_Click

End Sub

Does the error occur when checking or unchecking the checkbox?

 

[MsLady]

Hi Rural dude
Sorry i thot u forgot about me. I deleted the other thread

It occurs when i check the checkbox.

I'll go try this your new change...
When i had Me.chkActive initially, it wasn't working. That's why i put "Me.ActiveControl"

 

[RuralGuy]

Sorry, I was out on the tractor getting ready for winter.

 

[MsLady]

Sorry, I was out on the tractor getting ready for winter.

ahhh winter is still far
Is it getting cold in your area already? It's raining heavily here in NY.

Thanks again, but I still get the same error. perhaps it's the sql. the quotes...maybe. I'll go play around with that.

 

[RuralGuy]

The trees are almost bare, we're starting to bump on freezing at night, and we'll have snow here before Holloween.

The changes I gave you "should" have posted a MessageBox with the Sql string just before you got the error. Do you *not* get the message box?

 

[MsLady]

Hi Rural guy,
I hope you had a nice weekend. i know i did

Yes, i get the msgbox with my values set correctly inside it, right before i get that error. Then when i uncheck, i get the msgbox again, right before the "3061. Too few parameters, expected 1." error comes up.

.

 

[RuralGuy]

That usually means a misspelled field name. Since you get it both on checking and unchecking then I question ShortID as a field name. Yes, I had a great weekend, thanks.

 

[MsLady]

I checked my fields, they are properly set
Could this be because my form calculates some fields from other form (getselectedweek() and Weekending)? This is the record source of this form.


SELECT * FROM dbo_LDR_Support WHERE (((dbo_LDR_Support.Resource)=shortID()) And ((dbo_LDR_Support.WeekEnding)=getSelectedWeek())) ORDER BY SupportID;

 

[MsLady]

I checked my fields, they are properly set
Could this be because my form calculates some fields from other form (getselectedweek() and Weekending)? This is the record source of this form.


SELECT * FROM dbo_LDR_Support WHERE (((dbo_LDR_Support.Resource)=shortID()) And ((dbo_LDR_Support.WeekEnding)=getSelectedWeek())) ORDER BY SupportID;

Actually, i take that back.
i don't think this should be the problem. Cos the fields are loaded with Weekending date and Resource when i load the form.

 

[RuralGuy]

You are treating shortID like a function! shortID() What is that all about?

 

[MsLady]

You are treating shortID like a function! shortID() What is that all about?

Yes, It is a function that gets the shortID of the user. It feeds it into the form correctly.
I just stepped into this project, i really don't know the history. But from what i can see.
i.e. ShortID=txtResource (on this form)