Pls help a newbie with Form/Subform

[sailorguy]

Hello,

I have gotten this far with help. I have a very small (and what I thought was simple) database with 4 tables as below. I want to assign a DID, DN, TN to each user, but each DID, DN, TN value can only be used once. I built a form with 3 subforms as below. I want a combobox to allow us to pick from available values of DID, DN, TN for each user. This is were I'm stuck. I think that has to do with binding on the combobox, but I can't figure this out. I'm still learning. Any help would be great!

I even included the DB if that makes it easier (with some dummy data). This seems like it should be one of the most simple things to do...

tblUsers
UserID
FirstName
LastName

tblDID
DidID
UserID
DID

tblDN
DnID
UsedID
DN

tblTN
TnID
UserID
TN

FormUsersMaster

UserID
FirstName
LastName

SubformDID

DidID
DID (Rowsource filters only those where UserID is null)

SubformDN

DnID
DN (Rowsource filters only those where UserID is null)

 

[sailorguy]

So, I have the combobox with the available options for the first user, but the same items are still available in the combobox for the next user. I need to filter the choices so that only the unused items can be selected. The DN, TN, DID can't be duplicates.

I'm stuck.

 

[lcook1974]

Not sure what you were after but take a look at this.

I added a table (tblPhoneLog)
Took out the UserId from the DID, DN, TN tables

Fixed the combo boxes (check the record source for the NOT IN() SQL code)

Look at the after update event on the UserName combo box.

The new form (frmUserMaster) uses the tblPhoneLog as the record source. The combo boxes are numbers that are stored in that table. you should then be able to run a query off that table with relationships to the other table and get your information.

Check the Relationships window for visual.

Hope this helps!!

Larry

 

[sailorguy]

Hi,

Wow, you've been a great help! I didn't realize that the extra table would be needed for this. That's the junction table I believe? Now I understand that part.

I'm not sure I understand what that code is doing on the afterupdate event. But I do see that the boxes work, so that each DN, TN, DID is only available once! Yippie! I couldn't figure out the SQL code needed for this....I'm keeping this one copied in my files

I am wondering why when I select the user in the UserName field it doesn't populate the other comboboxes with their associated data? That way the same form can be used to assign info to new users and existing users.

Could I now create another form which will show all the users, with each of their assigned records all at once? So I could have all users listed, and see which don't have records assigned to them, and assign as needed. This is something I will work on.

Thank you again, so much for the help.

 

[lcook1974]

I am wondering why when I select the user in the UserName field it doesn't populate the other comboboxes with their associated data?

What do you mean by this? When you select a user, you want the combo boxes to show what numbers this user has? (is that correct)

Could I now create another form which will show all the users, with each of their assigned records all at once?

yep, you should be able to run select query and show who has what.

Glad I got you pointed in the right direction!

let me know if I can help out more...

Larry

 

[sailorguy]

What do you mean by this? When you select a user, you want the combo boxes to show what numbers this user has? (is that correct)

Yes, i see there are 2 limitations to the way the form is now. The username drop-down will still include users with numbers already assigned, but it doesn't show the assigned numbers for those users. So it's hard to know who still needs numbers assigned.

Potentially this one form could allow assigning numbers to new users, and reviewing numbers already assigned to existing users.

 

[lcook1974]

Do you need the UserName to drop off after the numbers have been assigned? OR will One user have many phone numbers?

 

[sailorguy]

Do you need the UserName to drop off after the numbers have been assigned? OR will One user have many phone numbers?

The user can only have 1 of each of the numbers (so they can have 1 TN, 1 DN, and 1 DID).

 

[lcook1974]

Hmmm....I'll try to play around with it a bit later. Gotta finish my real jobs work.

So If User 1 has a DID....then you want to see all the records that have numbers assigned to them, then go back and assign a TN or DN or both? is that right?

 

[sailorguy]

Hmmm....I'll try to play around with it a bit later. Gotta finish my real jobs work.

So If User 1 has a DID....then you want to see all the records that have numbers assigned to them, then go back and assign a TN or DN or both? is that right?

Yeah, I was trying to play around with yours to see if I could do it....nope.

Each user will be assigned 1 of each of those numbers (though in some cases they will have just 1 DID, and I TN, but no DN) or vice versa.

 

[lcook1974]

Maybe you could use something like this as another form or incorporate it somehow. I think it may help get toward a good solution.

Check out Form1 After update code....it will populate a list box. That would be a good way to tell what numbers people have.

http://www.access-programmers.co.uk/forums/showthread.php?t=182671&highlight=Dlookup

 

[sailorguy]

That is helpful and I think I can use that. But i did try a different direction though. I got a combobox on a main form (TblUsers Form) with text boxes on the sub form (TblUsersSub Form). This filters the combobox to show only users with no numbers assigned. Now what I want, is on the subform, to select the numbers to assign using combo boxes and update the user record. I've tried this a million ways from Monday without any luck. Do you know if this is possible?

This is what will show "new users" without numbers. I want to assign the each number to the user, then select the next "new user" and assign his numbers. Maybe I'm thinking about this totally the wrong way...

 

[lcook1974]

Working on a solution for you...I'll get back to you today

 

[lcook1974]

See if this is something that can help out. I'll continue to work on a "look up" for you in case you need to add numbers to someone.

open the frmUserMaster

 

[sailorguy]

Wow! That is too awesome. That works just PERFECTLY for that part of the process. It seems so easy when I look at what you did. I'm still just not looking at things the right way I guess. You're right about your second point...that's also important functionality. I was going one step at a time!

Thank you again for your kind help to me.

 

[lcook1974]

Okay...try this out.

Hope this helps.

Have a HAPPY NEW YEAR!!!!

Larry

 

[sailorguy]

Hi,

I hope you're still following this thread.

Thank you, that is absolutely splendid! I have used this and built around it to make it work for me. Thank you!

I have one question...and I think it is related to the code in the frmUserMaster form. If I try to assign only a TN and DID number and add the record I get this error "You can't go to the specified record". It appears that it will require each of the fields to be filled. Is there a way around this?

 

[lcook1974]

Hey Saliorguy...Did you get this?

 

[lcook1974]

I think this will correct whatever was wrong.

 

[sailorguy]

Crap, yeah I did. I think I just had the wrong version going....My bad.

I added a form to add/edit users into the tblUsers table. Just something very simple. And then I realized that deleting the user from the Users table doesn't delete their associated records in the other tables. This must be because the relationships are not set in your version of the database. I did a test by creating the table relationships to allow cascade update and delete. That works (I mean the other tables are updated when I delete a user) but it causes the error I mentioned in my last post. (The form requires data in EACH of the fields (DID, DN, TN) and can't leave any of them blank).


I hope you understand what I mean. Is there a way around this?