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

[jzwp22]

Glad that worked out for you!!!

 

[hardhitter06]

Jz,

Before I try and create my search queries/forms, I figured I should ask you what might be the best way to design this so hopefully you don't need to fix it at a later date haha.

Ok, two searches:

1. By Username, I would like to search a username that will display the USERNAME, ACCOUNT NUMBER(S), and VENDORS selected. I'm thinking either a Continious form or Datasheet because there will probably be multiple records/lines, but let me know what's best. Also, could you let me know what tables I have to use and what fields so I make sure I'm pulling from the right spot. With these relationships, I'm kind of knew so I'm expecting this to be a little more complicated.

2. By Account Number, I would search an Account Number that will display the ACCTNO and just the USERNAME(S) assigned. Again either a continious form or datasheet (w/e you think best).

TY

 

[jzwp22]

The design of the form/subform that opens to give the results of the search is really your own preference. I generally prefer to display the subform results in datasheet view, but depending on what the user wants to be able to do, I might display the subform in form view so that I can add command buttons and such. If you have many subforms, you might also look at tabbed subforms; they generally make the form look much more organized. You only have 2 subforms at this point (accounts and vendors), so it is not much of an issue. Right now you can use your frmUsers to display the search results by username. You can change the data entry property dynamically to no so that the form opens to show existing records.

With respect to the search form itself, I generally use combo boxes & a command button. Combo boxes are generally more user friendly--the users don't have to remember a number, a format (as in the case of your research account numbers) or a name. They just select what they need. Have the user select an item from the combo box and the have code that opens a form with the data filtered based on their selection. For your account numbers, you may want to use cascading combo boxes. The user would use the first combo box to select the type of account # (State or research) and then that would filter the next combo box to show only the account numbers of the type selected. Then a command button would be used to open the form with the filtered data.

 

[hardhitter06]

The users will never be able to add/edit/delete from a search so I think that will make life easier because I just need to find a friendly way to display the data.

I also don't know if this is going to be a problem but a user will have multiple records tied to their user name. For example, today, I have two accounts to add with a Vendor, in the Username field, I am going to enter "Josh" and add the other data. A week later, i have 3 new accounts with 2 vendors and again I'm going to add "Josh" under the username. So that is what I meant about having multiple lines.

I guess I will tackle the Username search first, because I think I can handle the other one. I think a datasheet would be best...because there is only a couple of fields and the datasheet will keep it in a neat and orderly format. My question is tho, how do I set this datasheet up because say the first entry of "Josh" had 20 Accounts..how would the AcctNO cell display all the accounts?


Would it look something like this:

Username Cell--------AccountNO Cell--------Vendor
---------------------AccountNO Cell--------Vendor
---------------------AccountNO Cell--------------
---------------------AccountNO Cell--------------
---------------------AccountNO Cell--------------
Josh (Record 2)------AccountNO Cell--------Vendor (Of the same name)
--------------------AccountNO Cell--------Vendor
---------------------AccountNO Cell--------Vendor
---------------------AccountNO Cell--------------


??

 

[jzwp22]

OK, now I am confused.

I don't understand what you are saying here:

I also don't know if this is going to be a problem but a user will have multiple records tied to their user name. For example, today, I have two accounts to add with a Vendor, in the Username field, I am going to enter "Josh" and add the other data. A week later, i have 3 new accounts with 2 vendors and again I'm going to add "Josh" under the username. So that is what I meant about having multiple lines.

Even though you are adding more accounts/vendors to a user, you will have 1 and only 1 user record for each user in your username table. To add new accounts/vendors you should not create a new record with the same user name. Having the same user name repeated over and over again in the table violates good database practices.

Now if you want to capture the event of adding additional accounts/vendors as you describe (...I am going to enter "Josh" and add the other data. A week later, i have 3 new accounts with 2 vendors and again I'm going to add "Josh...) and tie the accounts/vendors to the event then that requires a different table structure. Today and then a week later describes two events of adding new accounts/vendors.

Can you explain your process a little further with respect to this?

 

[hardhitter06]

Please Bear with me...

I've already undergo trying to change the username field on my Input form. I would like it to be a combo box where you select it on the Input form and if it isn't in the list, it will ask if you want it to be...just like the frmAddAccounts. I'm having problems with this, I might need you to take a look.

Anyways,

Let's say the first time a user enters a record, they have 3 accounts and 2 online vendors.

The second time around, this same user has to add 2 additional accounts and possibly another online Vendor.

So I guess I would have the user search by their username from a drop down on the Menu Form??...to retreive their previous entry/entries. Here, they would add 2 more accounts and add one more online vendor. (It is important to note that the Username ties into the Online Vendor Systems, not the Account Numbers. Meaning the new account numbers can be added to the existing list as opposed to trying to figure out a way to show that the first 3 accounts belong to these online vendors while these 2 new ones apply to these new vendors (different than the first 3) all under the SAME username....)


So what I need help/clarification with as followed:

1. I need help making this not in list combo box for Username.
-I have created the frmAddUsername and have used your code, but I might not be taking all that I need or making all the right adjustments because it isn't working right.
It is asking me when the username isnt in the list if I would like to add it...it opens up the frmAddUsername, but the username isn't being populated and my Add Command button isn't working right.

2. This is more on design/approach. Since we are only making one record under one username, I need to be able to add data to my subforms. Is the best way to search for a username..is with a combo box where you can select an exisiting username and it will pull up the record so that you can just add to the subforms? I'm pretty sure I can set that up.

I think if you can help me with my "Number 1", I will be able to do the second part.

I will attach my database so you can see whats going on with this frmAddUsername..and the Not in List Code.

TY

 

[jzwp22]

Before we go further with any forms, the following implies that the account number is tied to the vendor. Am I reading that correctly?

Meaning the new account numbers can be added to the existing list as opposed to trying to figure out a way to show that the first 3 accounts belong to these online vendors while these 2 new ones apply to these new vendors (different than the first 3) all under the SAME username....)

 

[hardhitter06]

No, Username = Vendor.

The account number has nothing to do with the online vendor Systems. So if you need to add more accounts, it doesn't matter because only the Accounts are tied with the Username.

Username = Vendor
Username = Accounts
Vendors does not equal Accounts (no relation)

 

[jzwp22]

How about the attached which uses the same form whether you are entering a new user or adding data to an existing user. You just select the option you want from your main form.

 

[hardhitter06]

Thank you...Looks great! The only thing is when you use the drop down of an existing and search for that form to open, it only displays the username and the subforms. When I go to design view and then return it shows all of them? Why is this and can that be changed?

 

[jzwp22]

I did that on purpose through the code behind the GO button on the main form. I figured that the user will not regularly need to change that info when they are just entering new accounts/vendors. You can remove the code if you want, just look for the following:

        Forms!frmUsers.Email.Visible = False
        Forms!frmUsers.Phone.Visible = False
        Forms!frmUsers.ShipToLocations.Visible = False
        Forms!frmUsers.ApproverYesNo.Visible = False
        Forms!frmUsers.Approver.Visible = False

 

[jzwp22]

I was playing around a little bit. Check out the stuff on your main form in the attached DB

 

[hardhitter06]

Wow man...thanks for taking the extra step of making that other search. It looks real nice and works great!

I think this is it...ready to be rolled out just on time, I'm going to play around with it and make sure its fool proof haha so my co workers can't mess things up. Again, thanks for everything..you def. were one the easiest people to work with on here.

 

[jzwp22]

You're welcome. As in most cases, once your users see what the database will do, they will ask for more. So be prepared to add more functionality!

 

[hardhitter06]

For this Misc Query...that shows all the information, is there a way to lock the query so that no changes can be made?

 

[jzwp22]

Do you mean lock the query results or the query structure? The users should not have access to the query, only its results (I usually hide the database window and only give the users forms to interact with the database). You can create a form based on the query and then lock the controls of the form so they cannot edit the data. Is that what you are after?

 

[hardhitter06]

Yes, thanks.

 

[jzwp22]

Go to design view of the form that is based on the query; highlight your form controls and then go into the property sheet->Data-->Locked and set to yes.

 

[hardhitter06]

Where does the AddUsername form come into play? Some how I was playing around with Data Entry and I was able to add the same user twice...I can't allow this to happen.

 

[jzwp22]

Frankly, I don't remember why I had the AddUsername form. I was probably going to use it and then decided to just open the form frmUser in the data entry mode instead. You can delete the AddUsername form.

Some how I was playing around with Data Entry and I was able to add the same user twice...I can't allow this to happen.

The easiest way to prevent duplicates is to go into the username field in tblUsers and set the indexed property to Yes(No duplicates). There are ways to do it with code also, but just setting the property is easier.