Subform making it very confusing to set up Search Queries/Forms

[hardhitter06]

Hi All (Access 2003),

I recently received help on the forum and was able to set up my first subform for a database I've been working on. I wasn't sure exactly how to do it, but worked my way through it with some help from other users on the site.

I thought life would be easy after that, but it isn't. My problem is I'm trying to set up two searchs on my frmMain (My menu form). I have one that is suppose to search by Username and I have another to search by account.

On my menu form, I have a textbox where a user will input the username they want to search. Respectively, I have the same thing set up for the account number but a combo box is used with all the accounts in the table. So the user will enter in the textbox with what they would like to search and then click a command button that will invoke a qry/form.

I would like the Username search, to show all records under that username showing the Username, Account Number, and 9 checkboxes.

I would like the Account search to return all usernames under that account along with the 9 checkboxes. (also what account number the User Searched)

Currently, I had this set up to kind of work...meaning, the searches worked but for example, my subform for one of my records had 3 accounts tied to it, instead of creating just the one user name with 3 accounts, it created one usewr name with 3 accounts 3 times...something having to do with my ID fields.

Right now, I was trying to fix that and now I'm getting nothing and really am unsure how to get past this subforms table.

I'm just going to post my database if anyone would like to take a look instead of me rambling on and on and then be more specific if need be.

Thank you.

 

[jzwp22]

Before you go too much further, I think you need to take a look at your table structure. In your user table you have a series of fields each related to a vendor (fisher, VWR etc.). These all refer to vendors so they are in a sense repeating groups which indicates that your table structure is not normalized. If multiple vendors can be associated with a user then that describes a one(user)-to-many(vendors) relationship which is handled differently than what you have. As an example, let's say you want to add a new vendor, you would have to alter your table structure and all related forms, queries and reports. I would not want to do that and I don't think you want to either. The better approach is to have a table that holds a list of vendors; each vendor is a record (not a field) in that table. Then you need to join the vendors to the respective users which will require another table. So the structure would look like this:

tblUsers
-UserNameID primary key, autonumber
-other fields related to the user

tblVendors
-pkVendorID primary key, autonumber (pk denotes primary key)
-txtVendorName

tblUserVendors
-pkUserVendorID primary key, autonumber
-fkUserNameID foreign key relating back to tblUsers
-fkVendorID foreign key relating back to tblVendors.

I have altered your table structure in the attached DB; I have also set up the relationships to help make it clearer.

At this point I don't know how the tblState ties in. Could you explain in detail what your application is designed to do? That way we can better help you with your table design which is the most important part of a successful database application.

 

[hardhitter06]

Thank you for the time you already have spent trying to fix my design..I knew there was probably a better way.

Anyways, this database is fairly simple.

I have many purchasing agents in my office who are incharge of ordering supplies from any of these 8-9 vendors (office max, fisher...ect). These purchasing agents would be considered the users or "username" (field).

So, purchasing agent Joe Smith is going to enter in his username on the input form. Along with the name, he MUST assign an account number that is used for purchasing from these Vendors (multiple usernames can share the same account numbers). Then I had it set up for the user to pick exactly what vendors he/she was registering for (Office max, fisher..ect).

Now I created a subform because any given user at any given time can register 1 to 50 accounts.


Lastly, which this part comes first, these accounts are either State or Research so whatever account the user is assigning to their name, will depend on the menu selection under "Input" of either "State" or "Research".

Is that clearer?

Oh, the tblstate was labled poorly because at first I thought I was going to have to make two tables one for state and one for research. tblstate would be better named as tblaccount

 

[jzwp22]

I'm still a little unclear about the account number. Is the account number a number your organization assigns to a purchase in order to distinguish how the purchase will be allocated in your organization, or is it the account number assigned to your organization by the vendor? In other words, is the account number assigned independent of the vendor?

 

[hardhitter06]

The account number is assigned by us to distinguish departments and/or grants from each other.

 

[jzwp22]

If that is the case, then the account number should be tied to the purchase. I assume you will ordering multiple items on an order. Can those items be split between account numbers or is the entire order attributable to 1 and only 1 account number?

 

[hardhitter06]

No, what it is is our Purchasing Agents (usernames) have access to certain accounts. Yes they will make orders with these accounts, but this database isn't capturing any orders. All it's capturing is the usernames, what accounts they have access to, and what vendors they have access to. Multiple usernames can have access to the same account though...

 

[jzwp22]

OK, that clears it up. Continuing with the earlier structure I proposed, we just need to add the account info

tblUsers
-UserNameID primary key, autonumber
-other fields related to the user

tblVendors
-pkVendorID primary key, autonumber (pk denotes primary key)
-txtVendorName

tblUserVendors
-pkUserVendorID primary key, autonumber
-fkUserNameID foreign key relating back to tblUsers
-fkVendorID foreign key relating back to tblVendors

tblAccounts
-pkAcctID primary key, autonumber
-AcctNo

tblUserAccounts
-pkUserAcctID primary key, autonumber
-fkUserNameID foreign key relating back to tblUsers
-fkAcctID foreign key relating back to tblAccounts

In terms of forms, I would base the main form on tblUsers and then I would have two subforms, one based on tblUserVendors (will show vendors attributable to that user) and another based on tblUserAccounts (will show the accounts attributable to that user).

 

[hardhitter06]

OK that seems to work. Couple questions??

1. I know what foreign keys are, but in terms of access, is there something I have to set up to make it a foreign key (for example, the primary key has that little key icon that you have to click to set it up as the PK), or by putting the same name in another table automatically does that for you?

2. On the subform for Vendors, the 8 I have listed, how will that look on the subform of the main form? Will it be check boxes again, or a drop down where I select one, if I have multiple vendors to add, I would add a new record of the subform and use a drop down and pick another one?...and so on and so forth?

 

[jzwp22]

1. I know what foreign keys are, but in terms of access, is there something I have to set up to make it a foreign key (for example, the primary key has that little key icon that you have to click to set it up as the PK), or by putting the same name in another table automatically does that for you?

The only thing to do with a foreign key is to make sure it is a long number data type which is the equivalent datatype to the autonumber. There is no key icon or anything else to do. The field basically becomes a foreign key when you establish the relationship between the parent table and the child table. Putting a similar name pkEmpID fkEmpID is a convention I use so that I can identify the relationship quickly especially in tables where you may have multiple foreign keys.

2. On the subform for Vendors, the 8 I have listed, how will that look on the subform of the main form? Will it be check boxes again, or a drop down where I select one, if I have multiple vendors to add, I would add a new record of the subform and use a drop down and pick another one?...and so on and so forth?

I usually have my subforms set up in datasheet view. Each vendor will appear as a separate line (record) in the view. You can use a combo box in the datasheet view to select the appropriate vendor, so you would create a new record for each vendor applicable to the user in the main form.

 

[hardhitter06]

Thanks JZ,

I'm going to set this up probably Monday, with the Input forms and what not. I may or may not need help on the Search Queries/Forms, if I do, I'll give you a holler.

Again, thanks for everything so far.

 

[jzwp22]

You're welcome. If you need additional help, feel free to post back.

 

[hardhitter06]

Am I suppose to make a query for each of my subforms on my Input form?

 

[hardhitter06]

Here is a copy of what I have so far...

Those two tables, UserAccount and UserVendors are not filling.

Also, for my Vendor list, for some reason I couldn't get it to display, I finally messed around and got it, but it's not working right.

 

[jzwp22]

Generally, I bind the form to the table, but you can use a query. You do have to be careful with the queries because depending on the type and number of joins, it might render the recordset returned by the query as un-updateable--in other words you would not be able to add new records. For example, for showing vendors associated with a user, I would create a subform bound to tblUserVendors and display the vendor via a combo box.

 

[hardhitter06]

See, I'm missing something here because I used tblAccounts and tblVendors for my subforms ONLY because they included the actual fields I needed (for input) (for example "acctNo" and txtVendorName") I tried using tblUserAccts, tblUserVendors, but those tables include fields (keys) from other tables and no field to input ...

 

[jzwp22]

The tblAccounts should not have the fkUserID it should only have fields associated with the account. The tblUserAccounts handles which users are authorized to handle which accounts. I have include a form (frmUsers) that has the 2 subforms.

 

[hardhitter06]

Well, this is going to be my input form I assume, so I changed the main form, with the subforms to Allow Data Entry.

When I get to the first subform, I go to add an account and I get an Access Error message stating: "You cannot add or change a record because a related record is required in 'tblAccounts'" This may be because I changed the AcctNo to a text box because new accounts can be added here so I don't want to choose from a list of what's already in the table.

The second subform works great.

Whats the deal with the first one though? Did i break it haha?

 

[jzwp22]

You cannot create a new account number with the way the subform is set up currently. I assumed that you only want your users to select from already approved account numbers. Do you want your users to be able to create new account numbers or is creating new account numbers more of an administrative function?

You have a couple of different options, but a lot of it depends on who in your organization has the authority to add new account numbers.

Option 1
Have another form to add a new account number and then direct the user to the frmUsers to add it to their username.

Option 2
Add code to the Not in List Event of the account # combo box that will guide the user to another form where they can create a new account number & then bring them back to the subform with the new account created and added to the user. This will take some Visual Basic for Application (VBA) code.

 

[hardhitter06]

The account number is not that serious in respect to who has rights to create it. I want to allow all users to be able to link an account number to their username at any time.

This is code I would need for option 2?:

Private Sub AcctNo___NotInList(NewData As String, Response As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPopupAddAccount"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

And on this form, I just need fields AcctID and AcctNo correct?