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

[hardhitter06]

Also, what would I have to change with the exisitng set up so I can get this code to work?

 

[jzwp22]

Actually it is a little more involved than that. See the attached DB; I've added the code in the Not In List event of the account number combo box. To see how it works, when you are in the account # combo box just type a new account number that is not in the list. I set it up to accept only numbers-no text since your account number field is numeric

 

[hardhitter06]

That is realllllly coool!! Thank you again.

 

[jzwp22]

You're welcome!

 

[hardhitter06]

Private Sub Form_AfterUpdate()
If Me.Funds = "Research" Then
Me.frmUserAccts.Form.fkAcctID.InputMask = "00000-0-00000;;*"
Else
Me.frmUserAccts.Form.fkAcctID.InputMask = "000000;;*"
End If

End Sub

How do I get this to work though?

 

[jzwp22]

Could you give a little more detail? Where and when are you trying to do this? Do you want to store the value with the input mask or just display the field's value in the specified format?

 

[hardhitter06]

Well, this was used on both my input forms (The State and Research one that looked and acted identical).

My "funds" field combo box (State/Research as choices), I had it defaulted to the corresponding Input form with this code behind the form. So that if someone choose to Input state, funds were defaulted to "State" and the Input Mask was a 6 digit entry. Research was 5-1-5 digit entry. This worked fine, but now that there's all these relationships, I'm not sure how to make it work. Especially because AcctNo field isn't on the subform...it is basically through relationships, but I'm kind of new to this and am unsure on how to make the fields (w/the code), "talk" to each other..

Also, I don't know if this is going to be further complicated with the form that opens when there isn't an account number in the list, but depending on State or Research, the user must enter it in the correct Input Mask. And with my co workers, I dont have a lot of faith for them to monitor themselves without this feature.

Let me know what you think

 

[jzwp22]

I assume that "funds" mean accounts and an account can be either state or research. Do you ever have a situation where 1 account can be both types? Looking to the future, could you have other types of accounts in addition to research and state?

If my understanding is correct & correct me if I am wrong, I would have a table to hold the types of possible accounts. This will allow for future expansion.

tblAccountTypes
-pkAcctTypeID
-txtAcctTypeName

Then in your account table, identify the type

tblAccounts
-pkAcctID
-AcctNo
-fkAcctTypeID foreign key to tblAccountTypes

Of course we will have to modify the form that opens through the Not in List Event of the combo box on the form we worked on previously. The code will also have to be modified. I would have the user enter just the number (no mask) and identify the type using a combo box. It relieves the users of any responsibility. You can display the number in the appropriate format on your forms, queries, reports etc. using an appropriate expression.

 

[hardhitter06]

OK...Yes State Funds or Research Funds aka State or Research Accounts.

There's only going two be these two types...always going to be in this same format.

By creating these tables, I will no longer need a State AND Research Input form...there will just be a general input form?

If I create the tables, you think you could help me with the code part. I have my form all clean and ready to go so we will truely see if this works?

 

[hardhitter06]

And for txtAcctTypeName, the two entries are State or Research correct?

 

[hardhitter06]

Here is the updated db with the tables. TY

 

[jzwp22]

And for txtAcctTypeName, the two entries are State or Research correct?

Yes, you are correct with respect to your question above.

I'll be happy to adjust the code, just attach the DB when you have made the changes to the tables.

Thinking ahead a little, is there any other data associated with a new account that need to stored in the tblAccounts i.e. other fields not yet in the table?

 

[hardhitter06]

Jz,

No there isn't, like I said, this is rather simple altho all the work you've helped me with has made it seem a little more complicated haha.

Just the Account Type, Account Number...and Has to be the right input mask givin the Type.

After that, I need to create two searches. One by Username and another by Account Number. And this DB is ready to go.

 

[jzwp22]

I noticed that you have funds (State or research) in the user table. Can a user work with both types of funds or only one type. I guess I'm asking whether the funds field should even be in the user table?

 

[hardhitter06]

They can work with both and will.

Yeah you are prolly right if we just added that AcctType field. I'm not sure though, you make the call.

 

[jzwp22]

If the formatting of the account number is critical then we should probably change it to a text field so we could apply input masks/formats. This will impact the code also. I will let you make the call on that before I work any further on the code.

 

[hardhitter06]

Yes that's fine...format is extremely crucial. So whatever it takes to apply these formats/masks is fine.

Great idea!

 

[jzwp22]

Can the account numbers contain alphabetic characters or only numbers?

 

[jzwp22]

I've added some code to check the account numbers based on the type selected from the combo box. Let me know if this is what you were after.
The attached DB has the modified code. I assumed that the lengths for state account numbers will always be 6 numeric characters and research account numbers are 13 characters long with the 6th and 8th characters being hypens and the other characters being numbers. You might try some account numbers that violate those assumptions to make sure everything works OK.

 

[hardhitter06]

Perrrrrrrfeeccctttt!!!