Help using modifying a sample form

[NWGal]

I'm trying out a sample db I downloaded from a code archive online. I've copied it all exactly, only changing names where needed, including in the code. My structure is this:
tblContact
tblRole
tblConRole (junction)
tblTEMPConRole
qryConRole
frmContactInfo - code in oncurrent event
sbfrmRole - code on checkbox afterupdate event

For some reason, although I have duplicated the code and replaced all my names, mine is not working the way the model does. My temporary table is deleting the records as it should but not replacing them.

Here is what I'm seeing - when I open the equivalent form in the model (with the temp table open at the same time so I can see what's supposed to happen) I can see each record in that temporary table says deleted, then when I close the form and close/reopen the table, the records (which should populate my list) are there again. In MY db, when I do the same thing, i.e. open table and then open form, the table shows deleted in the records, but on the form the subform is now blank, and when I close both, the temporary table has no records.

Can someone look at my code and see what I need to fix. As I said, the structure is exactly the same in both db's including relationships, format, everything.
Only the names have been changed.

Thanks

[HansV]

Did you intend to attach the database (zipped)?

[NWGal]

Did you intend to attach the database (zipped)?

Whoop's! Yes, but now that I'm not seeing how to do this? I don't see an attachment link

[HansV]

See Tip: adding an attachment using the prosilver skin or the post below that, depending on the "skin" that you use in Eileen's Lounge.

[NWGal]

See Tip: adding an attachment using the prosilver skin or the post below that, depending on the "skin" that you use in Eileen's Lounge.

Oh, I see, sorry about that...here you go

[HansV]

1) In the code behind the subform, you try to insert a record with ConID and RoleID and into tblConRole, but the field RoleID is not present on the subform.
To correct this, change the text box on the subform to a combo box with the following properties:
Name: RoleID
Control Source: RoleID
Row Source: tblRole
Column Count: 3
Column Widths: 0";0" (this hides the first two columns, so that the third column, RoleName, is displayed.

Personally, I'd place the check box to the right of the combo box, because the user must select a role first, and then tick the check box, not the other way round.

2) In the Form_Current event procedure of the main form, just above the line 'Else', you assemble a SQL string, but you don't actually execute it:

Code: Select all

        strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID where [qryConRole].[RoleId] is not null "
        'execute the query to create the temp table
    Else
        'disable the subform

Change it to

Code: Select all

        strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID WHERE qryConRole.RoleID Is Not Null "
        'execute the query to create the temp table
        CurrentDb.Execute strSQL, dbFailOnError ' *** THIS IS NEW ***
    Else
        'disable the subform

See the attached version:

TestZip.zip

[NWGal]

1) In the code behind the subform, you try to insert a record with ConID and RoleID and into tblConRole, but the field RoleID is not present on the subform.
To correct this, change the text box on the subform to a combo box with the following properties:
Name: RoleID
Control Source: RoleID
Row Source: tblRole
Column Count: 3
Column Widths: 0";0" (this hides the first two columns, so that the third column, RoleName, is displayed.

Personally, I'd place the check box to the right of the combo box, because the user must select a role first, and then tick the check box, not the other way round.

2) In the Form_Current event procedure of the main form, just above the line 'Else', you assemble a SQL string, but you don't actually execute it:

Code: Select all

        strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID where [qryConRole].[RoleId] is not null "
        'execute the query to create the temp table
    Else
        'disable the subform

Change it to

Code: Select all

        strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID WHERE qryConRole.RoleID Is Not Null "
        'execute the query to create the temp table
        CurrentDb.Execute strSQL, dbFailOnError ' *** THIS IS NEW ***
    Else
        'disable the subform

See the attached version:

TestZip.zip

I fixed the code, but re: #1, I don't want to use a combo box because it kind of defeats my purpose. The user would have to click to repopulate the list before being able to choose from the list. I am trying to get my db to act like this one:

InsteadOfMultipleYesNoFields2K (1).zip

[HansV]

Thanks - now I understand the purpose, and I could compare the code in your database with the original code.

Your code inserted records into tblConRole instead of tblTEMPConRole in one of the SQL statements, and you added a superfluous WHERE-clause to a SQL statement, causing it to insert too few records.

See the attached version.

TestZip.zip

[NWGal]

Thanks - now I understand the purpose, and I could compare the code in your database with the original code.

Your code inserted records into tblConRole instead of tblTEMPConRole in one of the SQL statements, and you added a superfluous WHERE-clause to a SQL statement, causing it to insert too few records.

See the attached version.

TestZip.zip

That did it...almost. When I go to add a new contact, I can't click on the check boxes.
Btw - I sure can't figure out how I goobered up the code when I thought I was being ever so careful in editing it, but THANK YOU for fixing it!!!!

[HansV]

That is a feature of the original database too.

The original database has a miniscule text box txtSentry on the main form, which is the last control in the tab order. It has an On Got Focus event procedure that saves the record, thereby enabling the subform.

As a consequence, you MUST use the Tab key to move to the subform. Clicking in the subform while you're in a new record om the main form won't work. (This is the same as in the original database).

I have added the text box txtSentry (the code was already present).
You won't see the text box because it is transparent and has height and width 0, but if you open the main form in design view, you can select txtSentry from the dropdown at the top of the Property Sheet.

TestZip.zip

[NWGal]

That is a feature of the original database too.

The original database has a miniscule text box txtSentry on the main form, which is the last control in the tab order. It has an On Got Focus event procedure that saves the record, thereby enabling the subform.

As a consequence, you MUST use the Tab key to move to the subform. Clicking in the subform while you're in a new record om the main form won't work. (This is the same as in the original database).

I have added the text box txtSentry (the code was already present).
You won't see the text box because it is transparent and has height and width 0, but if you open the main form in design view, you can select txtSentry from the dropdown at the top of the Property Sheet.

TestZip.zip

!!! Once again you have been a life saver as this was driving me batty. Plus, now I have my own working multiselect checkbox form if I want to use it again. Now I can head off to sleep happy, happy, happy. :)