Field with 1 to 50 entires..how to design

[hardhitter06]

Good Morning,

Access 2003.

I'm creating a Vendor Input form that includes a field named Accounts.

For any Vendor, they could have 1 account all the way up to 50 account numbers that need to be logged.

I would also like to be able to search by each account number.

Now my question is, what would be the best way to design this field for my form.

I thought about using a large text box that the users can enter in all of the accounts and seperate them by a comma, but this option wouldn't allow me to search.

I also thought about using multiple fields like Account1, Account2, and so on, but since some Vendors might have 50 accounts, that isn't an option.

Would I use a subform (IF so, can someone explain how to design a subform since I have never done that)?

Or is there a better idea?

Please let me know if you have some free time and thanks in advance.

 

[pbaldy]

It sounds like you need a related table, with fields for Vendor ID and account number. A vendor with 10 accounts would have 10 records.

 

[hardhitter06]

So I would have two Input forms? One to add a vendor, and a second to add a account to a vendor? Is that what you mean?

 

[pbaldy]

I'd probably use a form/subform. The form would be bound to the vendor table, the subform to this new table. Master/Child links would keep them in sync with each other.

 

[hardhitter06]

Question about subforms, do I make the table seperate from the Input Form with those two fields (VendorID, Account#) and a corresponding form with the two fields and then add this new form into my Master Input form?

 

[pbaldy]

Yes; if you use the wizard to add the subform to the main form, it will ask you about the common field and set up the master/child links for you.

 

[hardhitter06]

Now on this new table with the two fields, I need to do a lookup value? for the VendorID? Do I look up anything for the account field?

 

[pbaldy]

Not sure what you mean. I do not use nor recommend setting up table level lookups:

http://www.mvps.org/access/lookupfields.htm

 

[hardhitter06]

Can you take a look at this and let me know what I'm doing wrong.

I simplified some of my Field names to explain my problem so let me clarify:

Vendor=UserName
Accounts=StateAccounts

I've attached my DB.

 

[hardhitter06]

IT keeps saying index or primary key cannot contain a null value. I recently added a query behind my subform with the two tables included but that isn't helping either. I'm stuck

 

[pbaldy]

I only have 2000 here, but I can look at it later when I'm on another PC.

 

[hardhitter06]

Thanks Paul, I'm sure it will take you a couple minutes to figure it out since this database is pretty simple right now. Can you let me know when you take a look at it?

Once I figure out how to do this once, I should be set from here out.

I should add, that depending on the UserName, the accounts to be entered are either State or Research. Both have different Input Masks and I'm not exactly sure if I'm going to have to have two subforms for each with the different Mask or what, but if you could show me how to make this subform work, I'm sure I could figure out what I want to do from there. Just wanted to let you know that.

If anyone else wants to take a shot in the meantime, feel free

 

[pbaldy]

For starters, the UserNameID field in tblState is an autonumber, and it should just be a long integer . On a related note, it's the primary key, so you would only be able to have one of each person(it's what's called a foreign key from the user table). You either want a new autonumber key field in this table, or make the combination of UserNameID and StateAccounts a compound key. With those two changes, I'm able to add states to new or existing users.

 

[hardhitter06]

Thanks Paul, I made the Data Type change to Number (Long Integer). I'm a little confused on what else I need to do to make this work?

 

[hardhitter06]

Like creating another autonumber field? Like what would I name it and does that field actually go on the form? And this new field, would it be a primary or foreigh key? And do I change the usernameID field from the primary key to nothing?

 

[pbaldy]

Right now, that field is the key on that table. That means there can only be one of them, which would mean each UserNameID could only have one StateAccount, which isn't what you want. You either need to add a new autonumber field to that table and make it the key field instead, or highlight both the existing fields and make them both keys.

 

[pbaldy]

Like creating another autonumber field? Like what would I name it and does that field actually go on the form? And this new field, would it be a primary or foreigh key? And do I change the usernameID field from the primary key to nothing?

You can name it anything you want, and it does not need to be on the form. The new field would be the primary key. If you highlight the new field and make it the primary, the key should automatically come off the existing one. Alternatively, highlight it and hit the key icon, which will take it off.

 

[hardhitter06]

Why didnt you say that the first time haha...kidding, that was simple.

While I have you, maybe you can give me guidance on the best way to accomplish this.

The state accounts will always be in this format, a 6 digit number: 123456; 234567..ect

The research accounts will always be in this format, 7 digits, hypen, 1 digits, hypen, 5 digits: 1234567-1-12345

Now, at first i thought it would be obvious to just create two user input forms (one State, one Research). But both can have a username tie into one or the other or BOTH. So I'm unsure if this can still work or if there is a better way?

 

[hardhitter06]

Is this possible, depending on the drop down for Funds, is there a way so that if you select state, you can get the input mask in a 7 digit format and if u pick Research to get it the other way? Or is that too complicating for coding?

 

[pbaldy]

You should be able to change the input mask in the after update event or the combo, such as:

  If Me.Funds = "Research" Then
    Me.frmStatesubform.Form.StateAccounts.InputMask = "000-00-0000;;_"
  Else
    Me.frmStatesubform.Form.StateAccounts.InputMask = "00000-9999;;_"
  End If

I just picked 2 random formats; you'd need to do yours.