Best practice for data entry form

[GingGangGoo]

I have a form for my db for entering each contacts information, i.e. name, address, etc.
On this form, I have set up a way of choosing one or more roles for the contact and the role and contact id go to a junction table so that this info is NOT stored in my main table. This is working fine.

I feel dense, but I can't remember how to do the following. I need to add the contact's source of funding for our programs. I don't want to add this to the main table, and in this case there can only be one choice of 3, so a one-to-one relationship. I'd also like to only see the object for adding this (subform?combobox) when one or the other of two specific roles are chosen, as not every role requires a funding source.

FYI - I went a different route setting up my role choices, because I wanted check boxes and that all works with a piece of vba triggered in the OnCurrent event.

I hope this makes sense. As I said, I feel like I should know how to do this, but for some reason, my brain has misfiled the information.
TIA

 

[AccessMSSQL]

Not sure why you wouldn't want to put the funding source in the main table?

If you have one of only 3 funding sources you should create a lookup table that lists all 3 funding sources. In your main table just have a FundingSourceID column bound to a combo box on your main form. If it truly is only a one to one relationship this should be fine. When you check one or the other of your checkboxes for roles just show or hide the drop down box. You would do it in the after_update event of the checkboxes.

If you don't want to do it that way just create a new table that would have two fields in it: MainTableID and FundingSourceID. Your primary key would need to be a combination key on both MainTableID and FundingSourceID. This assumes that you also have a fundingsources table with a primary key of FundingsourceID. Also I always add an Autonumber field to my tables regardless of if it's the primary key or not.

So you would have:
TABLE:MainTableFundingSources
MainTableFundingSourceID AUTONUMBER
MainTableID (foreign key to your main table)
FundingSourceID (foreign key to your funding sources table

TABLE:FundingSources
FundingSourcesID autonumber primary key,
FundingSource text unique index

Hope this helps.

 

[GingGangGoo]

Not sure why you wouldn't want to put the funding source in the main table?

If you have one of only 3 funding sources you should create a lookup table that lists all 3 funding sources. In your main table just have a FundingSourceID column bound to a combo box on your main form. If it truly is only a one to one relationship this should be fine. When you check one or the other of your checkboxes for roles just show or hide the drop down box. You would do it in the after_update event of the checkboxes.

If you don't want to do it that way just create a new table that would have two fields in it: MainTableID and FundingSourceID. Your primary key would need to be a combination key on both MainTableID and FundingSourceID. This assumes that you also have a fundingsources table with a primary key of FundingsourceID. Also I always add an Autonumber field to my tables regardless of if it's the primary key or not.

So you would have:
TABLE:MainTableFundingSources
MainTableFundingSourceID AUTONUMBER
MainTableID (foreign key to your main table)
FundingSourceID (foreign key to your funding sources table

TABLE:FundingSources
FundingSourcesID autonumber primary key,
FundingSource text unique index

Hope this helps.

Thanks - I took the simple route and have it working fine, now when I open the form the combobox is hidden, and when I check a role, it opens.
Last question - how do I get it hide again after I make a choice.
In the after update of the combobox I put this me.combobox.visible=false but I get an error message that I can't hide the object while it has focus.

 

[AccessMSSQL]

You need to move the focus to another control before you hide the combo box.

ControlName.SetFocus

 

[GingGangGoo]

You need to move the focus to another control before you hide the combo box.

ControlName.SetFocus

Ok that makes sense :0)

Now that I figured that out, I'm playing with using an option group, like this: Funding? Yes or No for triggering the combobox instead of having that tied to a specific role.
I set this up so when I click yes, the combobox opens, when I click No, if it's open it closes. Now the next question is: how do I set the No option to clear the field in my underlying table so that if the person had funding, but now doesn't, I can edit the contact, choose no, and that frecord is changed to a null in the table?

 

[AccessMSSQL]

You need to write some VBA code to delete the record in your onchange of the option group

If me.optFunding = 0 THEN '(or whatever value your NO value is set to)
Currentdb.execute("DELETE FROM tblFundingSources WHERE ID = " & MainFormID
END IF
YOu need to delete where the funding source record = the record of your main form that is related.

 

[GingGangGoo]

You need to write some VBA code to delete the record in your onchange of the option group

If me.optFunding = 0 THEN '(or whatever value your NO value is set to)
Currentdb.execute("DELETE FROM tblFundingSources WHERE ID = " & MainFormID
END IF
YOu need to delete where the funding source record = the record of your main form that is related.

Ok, I understand this part
If me.optFund=2 Then
But I'm confused on the second part. You have tblFundingSources, but my table is not the lookup table, tblProvider, but rather my main contact table, tblContacts. I don't want to delete anything from my lookup table right?
I'm still very new to VBA as you can tell.
Thanks

 

[AccessMSSQL]

Oops. sorry. No, you just want to clear the funding source selected in your main contact form, right?

Thats easy just change my code to

if me.optFund=2 then
  ComboBox = Null
end if

ComboBox is the name of your funding source drop down that the user selected if they chose yes in the option

 

[GingGangGoo]

Oops. sorry. No, you just want to clear the funding source selected in your main contact form, right?

Thats easy just change my code to

if me.optFund=2 then
  ComboBox = Null
end if

ComboBox is the name of your funding source drop down that the user selected if they chose yes in the option

Sweet! That works great! Thank you so much.