dropdown list to depend on other field

[USEN]

Hi you guys...I'm stuck again.

While awaiting your reply to my earlier posting, I decided to go ahead & set up the next new "main" form. I am working on the first subform that goes on it...same exact principals as the first main form for the Freelancers only this one is for the Projects and may have several people working on each one. I've got my list box and the control which places the selections onto the junction table. Then I've got my combo box which is supposed to pull those selections back off of the junction table & post them back to the subform so that they can be seen & deleted if necessary. Since we're dealing with the freelancers here, I thought it best to include 3 fields in this combo box...the ID # which is the bound field, and the first & last names. At first I set the field length for column one to "0" because I don't need to see the FreelancerID #; however I was having problems so I changed it back to .5". When you click on the down arrow, you see all 3 fields (or just the 2 when I hide the first one)...but, only the first field visible field will actually post in the box (I have it wide enough so that's not the problem...which since that's usually the Last Name, it would be OK since they see the first name when they choose the arrow so know who they are choosing. The only problem is that when I try to do anything else, I get an error telling me that the Last Name field can't be left Null in the tbl_Freelancer_Information. I can't figure this out because that field is NOT null. I had the Row source as a qry at first, but changed it to an SQL statement like I did the one a few days ago because it seemed to be deleting from the Project table rather than the junction table. Now I'm not sure if it's working correctly because of this problem. I do know something isn't quite right even before this particular step because on the other form, the moment I make the selections from the listbox, they automatically post in the combo box below...and this one isn't doing that.

What am I doing wrong?

Again, I appreciate your assistance. What would I do without you guys? I am learning so much from you both, yet still feel as if I don't know anything. It's especially quite confusing having to worry about setting properties for the same subform in 2 different places...one while the subform is open in Design View and the main form is in Form View...and the other when both the main form and the subform are opened in Design View. Why is this & is there a way around it?

I patiently await...

USEN

 

[USEN]

Figured you might need another update since I've added the new "main" form & subform.

 

[pbaldy]

I don't want you to think I'm ignoring you, but I've been busy. The PGA tour is in town, and I spend a fair amount of time working at the tournament as a volunteer. I have to cram the "emergencies" that come up at work into the remaining time, leaving me little for things like this. I'll take a look when I can get time.

 

[USEN]

No problem, Paul. I DO appreciate you telling me. Actually, I'm kinda enjoying the break from it...lol. Employer is on vacation at the moment so I'm no too pressed...just so I have time to get a lot done before she returns. I think it will go much faster now...at least I hope it will. I though that once you gave me your brilliant idea about the freelancer's "rates" that all would be solved. I wasn't expecting this last stump I tripped over on this new "main" form. Guess I can't let you think you're going to be rid of me so easily...lol.

Whenever you get the time...just don't overlook that I now have 2 problems rather than just one...

USEN

 

[USEN]

Hi Paul,

I did quite a bit of work on the database today so I'm posting an updated copy.

USEN

 

[USEN]

Hi Paul,

Just checking to see if you've had time to look at my files yet. Employer is back from vacation and will need an update soon. If at all possible I'd like to at leat have the Freelancer tables complete and out of the way...so the problem concerning the "rates" is more important than the problem on the Project table.

I know you're busy with the tournament, but would greatly appreciate if you find any spare moments.

Thanks,
USEN

 

[pbaldy]

Well, the tournament ended Sunday but then my daughter came to town to visit. She's leaving on a Peace Corps mission next week for 2 years, so I took off work to spend time with her Monday and Tuesday. Hopefully I will get to your project after I get caught back up at work.

 

[USEN]

Thanks Paul!

 

[pbaldy]

I'm not sure it fits, but my first thought on rates is to add a field to the junction table for the rate. That gets you the rate associated with each person/skill combination. For that to work, there would have to be consistency regarding how each skill is billed. IOW, everyone would have to charge hourly for data entry, by word for translations, etc. If that's not the case, I see an unholy mess ahead.

 

[USEN]

I think we have that "unholy" mess ahead...lol! Couldn't the field for the rate just be a text field where they could type in say..."$25.00/hour" or "$.15/word"...or even a flat rate "$300.00 Total"? ....or to make it searchable within a query...perhaps 2 additional fields in the junction table...one for the "dollar figure" and a 2nd for the "per" figure? Perhaps this 2nd additional field could even be limited with a combo box?

Boy! I wanted some first hand experience other than designing a "simple database for myself", but this one has turned out to be a humdinger for a newbie, hasn't it? If I ever get done with this one, I'll feel like a pro if I ever have a chance to do it again...lol! Seriously, I could NOT be doing this had I not found you. Thanks Paul! You are a LIFE SAVER!

 

[pbaldy]

You could do either, you'd just have to do the comparison manually instead of the program doing it (without jumping through some hoops anyway). The other wrinkle is that I don't think you'll be able to populate that field from the multiselect listbox. I think you'll have to add that data later. I suppose you could have the code that adds the person/skill combinations stop at each one and ask for the rate, at which point you'd type in "$25.00/hour". Then it could include that field in the append.

You are certainly learning how to run before learning to crawl.

 

[USEN]

Paul...can the multiselect listbox contain 4 columns...the first would be the (preferably hidden) Autonumber, the 2nd the actual skill, and 3rd a blank currency field, and 4th a blank text field for the "/hour, etc."

This does seem rather complicated. You know hwat...It's no big deal if they have to click the control that enters the skill and once entered has to add the additional 2 columns of info. I should be able to place the 2 extra fields right next to the choices...placing them in the same area where they appear with the Delete box next to the items. Couldn't I simply insert these extra 2 fields (1 currency & 1 text) between the generated list that appears and the deletion control for each one? That is IF I can get the subform to list them correctly....that's the problem I'm having for some strange reason on the other form.

At least, I THINK I can see the light at the end of this dark tunnel.

Now I've got another problem with another assignment in Word where my Macro isn't behaving. I'm trying to get it to work with any document, but it seems to want to set the VBA code to work only with the document in which I write it. I designed it while creating a template & it doesn't want to work with the "documents" that are generated by that template. First one problem now another...jeez, I'm getting dizzy.

 

[pbaldy]

Sorry, I missed this. The listbox can have the columns, but you'd still be populating the table with blanks, as I assume you couldn't populate the listbox with all the possible options.

 

[USEN]

Thanks Paul! I think I'll try my last idea. Hopefully it will work.

 

[USEN]

Hi Paul...I'm almost there! Whoopie! Just need a little more help with coding, I think...hope! Finally figured out how to fix the main Project form: frm_Project_Information, it's correlating tables: tbl_Project's_Freelancers and tbl_Project_Information, and the subform: sfrm_Project's_Freelancers. I also was able to complete all the related items for the main Client form: frm_Client_Information, and it's table: tbl_Client_Information. That just leaves the infamous main Freelancer form. All the subforms are working perfectly except the last one: sfrm_Freelancer's_Skills_and_Rates. It's related tables are: tbl_Freelancer's_Skills_and_Rates, tbl_RatePer, tbl_Skills, and tbl_Skills_Categories. The skills list on the subform fine, but the command button cmd_Store_Skills, isn't working for some reason. The VBA coding is the same for it as all the other subforms except that there are extra fields. I can also enter data into the other fields and the RatePer combo box works fine; however, there is a conflict isomewhere in the RatePer that perhaps might be the reason and/OR the problem with the command button not working properly because after I enter the the info for the other fields and try to see if they will post to the tbl_Freelancer's_Skills_and_Rates table, I get an error message saying the records can't be saved because it's missing a linkng record in the tbl_RatePer. Seeing as that table has only 2 fields, the ID# and the list of items...both of which are being utilized in the combo box, I don't understand what it's lacking? The Secondary might also be failing as it is set up exactly like the Primary only the Primary is required and the Secondary isn't. I started out with the Primary and Secondary fields in the table as Text with the combo box only on the form. When that didn't work, I also tried changing them to combo boxes in the table lookup tab...didn't help.

...so, I KNOW for certain that the Store command button isn't working because I've selected some skills and then clicked the button, saved the record, and then checked the table. They weren't there. I don't know if it would work just entering them in the detail section because I get that error & it won't save. It appears to because the continuous form drops down and give me a new empty record, but that doesn't necessary mean that it would post to the table.

I sure hope you can solve this, Paul. Other than a few more queries...which are a breeze, I'm almost done! Just need to get these skills and rates working.

Once again...I'll be in your debt. What am I saying? I'll NEVER get out of your debt...lol! It's funny, though, when you first began helping me, I undersood just enough of the coding you gave me to get by. Now...as often as I've had to study it searching to discover why this command or that form or table wasn't working properly...I may not understand every little detail, but I guarantee you that I've got a pretty darn good feeling for what it say...LOL! The only part that still confuses me a little is the VALUE part of the SQL string...especially all the commas and quotes. After I get through with all of this, I'll take the time to try to find it in one of my many VBA and Access text books. As difficult as this has been, I've loved every minute of it. I thrive in learning this stuff.

Hope to hear from you soon, though, so that I can get this part of it out of the way for now as then I'll have to begin the front end of it with the data entry. Oh...there IS one other thing I might tackle and that's the Switchboard and perhaps the "splash screen"...although I'm not certain what that the purpose of it is, so perhaps not; but I do think it needs the Switchboard. Hope it isn't something hard to do. I've never tried one of those before. Please tell me it's easy...

USEN

 

[pbaldy]

I've only got a minute, but your error comes because of relationships between tables (might be the evil lookup field). This works:

strSQL = "INSERT INTO [tbl_Freelancer's_Skills_and_Rates] (FreelancerID, [Skill_ID], Primary_RatePer_ID, Secondary_RatePer_ID) " _
       & "VALUES(" & Forms!frm_Freelancer_Information!FreelancerID & ", " & lsb_Skills.ItemData(varItem) & ", 1, 1)"

Primary_RatePer_ID is set to required, so it would have failed because of that too.

There's a tutorial here about building SQL in VBA that might clear up all the quotes and such:

http://www.mdbmakers.com/forums/showthread.php?t=4895

 

[USEN]

Thanks Paul, as usual. I'll let you know if that fixes everything for me. As far as the lookup field, I only used that as a 2nd means when the "test" field failed to work.

I'll definitely try out the tutorial as well.

TTYL...
USEN

 

[USEN]

Almost there, Paul...just need a little more advice...Please!

Hi Paul...I'm so excited!!! I got the rates working perfectly now with the adjusted code. Only problem I'm having now with it is that the delete button isn't deleting the record.

Afterwards, all I have is the Switchboard. I've never done one before. Does the wizard do it OK or do I need to do it from scratch? Please tell me it's a simple thing to accomplish.

USEN

 

[pbaldy]

The button has gotten disassociated from the code (it happens once in a while). If you go into the properties of the button, you'll see there's nothing in the on click event. Put the cursor in that line then click on the ellipsis (...), which should bring you to the code (choose code builder if presented with 3 options). That will re-associate them.

I'm not personally a fan of switchboards (the kind the wizard builds), though that doesn't mean there's anything wrong with them. I simply build a form with buttons to open the forms I want opened.

 

[USEN]

Thanks Paul...I'll hollar if I can't get the delete to work. Meanwhile...about the Switchboard.

So what you're saying is that all I need to do is to simply build a form with cmd buttons on it that instruct Access to open the other forms. And the point of that is? Also, what tell Access to bring up the first form? It's been quite awhile since I've started Access from the "front end", once you're in say the "Main" Freelancer form, how do you get back to the Switchboard? Do I need to place a cmd button on each of the forms that will exit that form and go back to the Switchboard? And finally, is there a way to place some kind of cmd button that will run the queries I've set up?

I'm so excited that this is nearly finished. Spoke to my employer a little while ago & she's very pleased with my work so far. Yea! Don't worry, I've given you a LOT of credit...lol. Oh...almost forgot, how can I password protect or something to prevent anyone from going in the "back door" and messing up things?

Thanks,
USEN