dropdown list to depend on other field

[pbaldy]

Ack! When I see a page full of writing my brain cramps up!

As to tables vs forms, I never let users directly into tables. They only enter/edit data through forms, so I'm with the boss on this one.

It sounds like your problem with the languages is that you only had one field in your table for language, and both controls were bound to it. Maybe the attached picture will help. You first want to establish a good table design. You wouldn't have a field in the main table for language, rather a related table with a one-to-many relationship.

 

[USEN]

Thanks Paul! I think I might be able to get somewhere with this. Next question...can I use the combo box I already have on my form to insert the data into my tbl_Freelancer_Information table (this appears to be the same as the "Person Table" in your example). It is currently set up with SQL to post the Native Language so it would have to be changed somehow to point the SQL to the Person/Languages Table? Right? Also, am I correct in assuming that the Person/Languages Table is what is known as the "Junction" table between the tbl_Languages and the tbl_Freelancer_Information? Which would automatically then post to the tbl_Freelancer_Information table through the established relationship? Does this also eliminate the need to use the Current event procedure to requery the combo box when navigating between records?

I'll start working on this new "junction" table. I think I may be seeing a light...although small...at the end of this tunnel I've found myself in....T-H-A-N-K-S!

 

[pbaldy]

You can call it a junction table, yes. Your person table (tbl_Freelancer_Information) would not contain any fields related to language, so no, that combo would not be the appropriate method. For this type of data you would typically use a form with a subform, where the form is bound to the person table and the subform is bound to the junction table. Master/child links ensure that the data on the subform relates to the person in the main form. To quickly populate the junction table/subform, you can use the multiselect listbox where the user would pick however many languages were appropriate, and you'd have code to add the records. A multiselect listbox always returns Null, so it can't be bound to a field.

Gotta leave, but I'll check in later this evening.

 

[USEN]

Hi Paul or Bob,

I'm back to work after some much needed rest. Thanks to both of you, I now have my "junction" table (tbl_Freelancer_Languages) set up with a 1-to-many relationship with both my main table (tbl_Freelancer_Information...using the FreelancerID field) and the tbl_Languages table (using the LanguageID field). It appears to be working great. Now...next question...Paul, I understand your explanation of usig my multiselect listbox on a subform to enable us to list all the languages each freelancer speaks, but how do I separate or otherwise indicate the person's "primary or Native" language from all the others he/she might speak? Can I still have a Combo box on the subform for this purpose in addition to a multiselect listbox for the additinal languages or will this once again cause a conflict due to both relying on the same field to populate the result? Also, since using MultiSelect returns a Null value, the only way I can make the listbox accept more than one item is to set the listbox as unbound...correct? Now...what do I use to get the selections posted to one of the tables that is connected to the Freelancer? I know it's done using VBA code, but I don't know which property to write the code to (Selected Property or ItemsSelected Property or ItemData Property perhaps?)...nor do I know what code I need to write. I assume that they have to be posted to the junction table rather than directly to the main table?

As usual...thanks for your advice,
USEN

 

[USEN]

Hi Everyone...

Since posting my last message, I added a Command Button (cmd_Store_Languages)to the subform which should transfer the items that are selected in the MultiSelect Listbox to the junction table. I think I've found the VBA code I need; however, I'm missing something & don't know what it is because when I try to execute it, I get an error message that says "Object needed". Here's my coding (the Error portion of which was added by the Wizard when I set up the Command Button):

Option Compare Database

Private Sub cmd_Store_Languages_Click()
On Error GoTo Err_cmd_Store_Languages_Click
Dim sfrm As SubForm
Dim varItem As Variant
Dim SelectedVals

Set sfrm = SubForm![sfrm_Freelancer's_Languages]

For Each varItem In lsb_Languages.ItemsSelected
DoCmd.RunSQL "INSERT INTO[tbl_Freelancer's_Languages]([Language_ID#])"
Next varItem

Exit_cmd_Store_Languages_Click:
Exit Sub

Err_cmd_Store_Languages_Click:
MsgBox Err.Description
Resume Exit_cmd_Store_Languages_Click

End Sub____________________________________________________________

Private Sub Detail_Click()

End Sub____________________________________________________________

Private Sub Form_Load()

End Sub



The Wizard also had a line that read:
DoCmd.GoToRecord , , acNewRec

I replaced this line with the DoCmd.RunSQL line. Do I possibly need them both?

I've come so far with this...I'd really like to get this one subform finished so that I can start over with one of the several others I have to do. I hate to go ahead & start on them, however, until I know for sure I'm doing it correctly. I located one seeminly simple solution to all of this posted by Mile O-Phile which simply placed everything in a "lookup fields" of the junction table. It was my same exact situation. I was just about to change mine to match his example when I happened to find an article entitled "The Evils of Lookup Fields in Tables", which quickly changed my mind (http://www.mvps.;org/access/lookupfields.htm). Figures that anything making life simplier would be BAD NEWS...lol.

Oh well, at least I'm learning a lot from this experience. I'm supposed to meet with my employer first thing Monday morning; so if anyone can possibly help me get this resolved before then, I would GREATLY appreciate it.

In your debt,
USEN

 

[pbaldy]

Try

  CurrentDb.Execute "INSERT INTO tbl_Freelancer's_Languages ([Language_ID#]) " _
                  & "VALUES(" & lsb_Languages.ItemData(varItem) & ")"

 

[USEN]

Hi Paul...thanks for the reply. I changed my code to match your suggestion, but it still gives me the error "Object requried". Here's the revised code:

Option Compare Database

Private Sub cmd_Store_Languages_Click()
On Error GoTo Err_cmd_Store_Languages_Click
Dim sfrm As SubForm
Dim varItem As Variant
Dim SelectedVals

Set sfrm = SubForm![sfrm_Freelancer's_Languages]

For Each varItem In lsb_Languages.ItemsSelected
CurrentDb.Execute "INSERT INTO [tbl_Freelancer's_Languages] ([Language_ID#])" _
& "VALUES(" & lsb_Languages.ItemData(varItem) & ")"
Next varItem
Exit_cmd_Store_Languages_Click:
Exit Sub
Err_cmd_Store_Languages_Click:
MsgBox Err.Description
Resume Exit_cmd_Store_Languages_Click

End Sub
Private Sub Detail_Click()
End Sub
Private Sub Form_Load()
End Sub

 

[USEN]

B.T.W. I'm placing the brackets around the table name because of the apostrophe. I see now that using that character is a problem so I won't do it anymore; however, at this state, I'm afraid to start changing names.

I've seen where some people attach their database so that you all can view it entirely. How can I do that?

USEN

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[USEN]

Thanks Paul...here's a "Copy of My Database"...renamed as such for confidentiality reasons with my employer. Please tell me what I'm doing wrong here. Keep in mind that most of the tables have yet to be placed on the form(s). Also, the Freelancer Type & Industry Experience listboxes that are on the main form were placed there before I learned about "junction" tables. I'm hoping to be able to make some adjustments & place them on subforms without having to completely delete them. For now, my main concern is just getting one group working properly...the Languages. I think that once I get one working, I'll be able to use it as a "template" to guide me with the others.

Thanks,
USEN

 

[pbaldy]

I changed quite a few things. This appears to work.

 

[USEN]

Hi Paul...thanks for the prompt reply; however, using the file you returned, when I click the command button on the subform, it gives this error:

"Operation must use an updateable query"

 

[pbaldy]

Which one? I just tested again, and it works fine (the languages spoken by freelancer subform).

 

[USEN]

Never mind, Paul...I copied the code into my original database and made the appropriate changes to the listbox & it works fine. I don't get that error. I do need some way of updating the records rather than appending to them should someone return to the subform to correct an error made when the data was originally entered. Is this possible? Since the junction table only indicates the Language ID, it would be difficult to know which record to delete using that table. Besides, from what I've learned on this forum...(probably from you...lol), the user shouldn't be given access to the tables except through the form so I need a way to correct entries via the form.

My next question will be for you to explain some of the corrections you made to to the code...especially the Dim strSQL As String statement. I need to understand the code you added/deleted so that next time, I'll be able to write it on my own.

B.T.W...you've been such a GREAT help! I tried to give you more Boy Scout points, but it wouldn't allow me to do so until I've "spread them around more". That hardly seems fair since only you & Bob have assisted me & I've already given him points as well. How can I spread them around when there's no one else to spread them to? LOL!

Regardless...You're wonderful!
USEN

 

[pbaldy]

In this type of situation I typically let the user add records with the multiselect listbox, but let them view/edit them with a subform. You bind the subform to a query instead of the table itself. The query joins the junction table to the languages table so you can display the text.

The main reason I added that line was so I could examine the SQL string to figure out why it wasn't working (using ?strSQL in the Immediate window), which turned out to be the incorrect bound column specified.

I appreciate it, but don't worry about the points; I don't. Your thanks are enough.

 

[USEN]

How do I go about bindind the subform to a query rather than to the table? Where do I go to view the table-to-subform relationship? I know how to view, establish, & edit relationships between tables & queries, but not forms or reports

USEN

 

[boblarson]

Here are instructions on changing your recordsource from a table to a query:
http://www.btabdevelopment.com/main...dsourcefromtabletoquery/tabid/75/Default.aspx

You do not set a table to subform relationship. Relationships are set in the Relationship window for tables and queries.

Subforms are related to the main form via the Master / Child links in the subform container control that houses the subform on the main form.

 

[USEN]

I'm really confused now. I checked to make certain... and as I thought, my subform is already linked to a query (qry_Freelancer's_Languages) rather than to the form. I did this so that I would be able to have a MultiSelect listbox on the subform that allows the user to select multiple records.

Problem is...once I use the command button to INSERT the selections and I close the form, when I later return to that record on the form, the subform no long indicates those choices. Is there a way I can retain the selections for each record; yet, have them clear if I move to a New Record?

Thanks,
USEN

 

[USEN]

Hi Everyone,

I've included the latest update to my database. thanks to Paul's assistance on the first one, I now have 2 working subforms...as far as posting the MultiSelect listbox selections to the junction table.

I still need to figure out what I need to do so that the user can return to the form/subforms and make corrections to the subform entries. Once I close the main form and re-open it, the subform selections have disappeared. They are still recorded in the junction table, but my user will not be using the actual tables...only the forms.

On the flip side, just the opposite needs to happen when I have the main form open and have finished entering everything for one record and move to a new record. Now I WANT the subforms to clear, but you'd know it...all the subform selections from the previous record carry over to the new record.

Can I somehow get the results from these two situations to reverse?

Thanks for all the assistance. I don't know what I'd be doing if I hadn't found you guys! This site is a LIFE SAVER! Whoever started it should receive a gold medal for certain!

USEN

 

[USEN]

I uploaded the attachment...what happened to it? Oh well, here it is again.

USEN