I am trying to do something that I thought would be simple but I guess I need to learn more.

I want the user to select a record in a drop down menu and then based on that selection have the correct form for data entry open up.

Drop Down menu has the following layout and the user searched by docket number htat is a unique field...

Code Client Docket
300 Client A 0120
400 Client B 0333
500 Client C 4545
600 Client D 3432
700 Cleint E 2343

Each client has their own personalized data entry form specific to their needs.

For example, they select a record in the drop down with a code of "300". That "300" code signifies it is Client A and the Client A data entry form needs to be opened up.

Oh and to make it even more confusing, I need it to open a separate form if the code is "none of the above" as well.

What am I missing here?

Thanks in advance for any assistance.

How many clients are there what is the form naming convention ? what appears in the combo / dropdown

Sounds like you need a configuration table to relate your forms to the specific Clients:

tblClientCustomForms
-------------------------------
CCFID, AutoNumber, PK (not used by your program)
ClientID, Foreign Key to the table holding your Client Information (tblClients)
FormName, text

The ClientID is included to insure that only valid Client Names are stored in this table.

Include one entry for ClientID = Default, FormName = [your default form].

On your form, after the record is selected, use DLookup to get the formname where the "Client" selected matches the ClientID name in the custom forms table -- set it to a variable (perhaps txtFormName). Then use txtFormName in the OpenForm command. If DLookup returns "", then DLookup the FormName where ClientID = "Default".

By storing the form/client names relationship in a separate table, you will never have to modify the actual code if clients are added/deleted/modified.

If you have 20 clients will you have 20 forms? What do you do as more clients are added to the database? Must you now create a new form for the new client before you can use the database for that client?

What you want to do is certainly possible, but how many clients do you have now? How unique is each form from the others? What is unique about the No Client form?

Having a separate form for each and every client does not sound like a very good approach, but that is just my opinion...

Jack

There will only be these 6 clients.

I thought of using only one data entry screen but it is for a factory where many people are doing the data entry and they want everything separate.

They need unique information per client to be entered. Basically it needs to be simplified so that the user selects the docket and only sees the data entry form specific to that client. They don't want a cluttered data screen - just the fields they need to enter.

Once the data entry is complete then the company specific report is auto-populated and prints out. There are 6 completely unique reports that get filed to the end client.

Currently it is set up so they select the docket in the drop down menu and then choose the button for the client data entry form to open based on the Client No. The client wants to get rid of this step and have the dbase do that automatically for them.

I thought it would be as simple as an If statement but I can't get it to work.

My other thought is to force them to have a universal data entry screen with all of the combined data and then do something with the print button to open only the report that is linked to the Client No. Is that an easier route to go? And if so how do I set up that print button to do this?


Thanks.

Jody

Jody -

I always get nervous when someone says that there will never be more than this many 'clients'. It seems that as soon as the db is finished that there is a new client that has to be integrated into the database... The reason I say this is because you should try and create the database so that it covers all eventualities. I realize that this is not alway possible, but certainly worth striving for...

Now as to what the client wants to get rid of... You can use a combo box to select the client and once one is selected the correct form will open...no need for the additional button...if I understand what you are saying... Something like:

Select Case Me.NameOfComboBox
Case 100
DoCmd.OpenForm "frmClient100"
Case 200
DoCmd.OpenForm "frmClient200"
...etc...
End Select

The above assumes that the ClientNumber (100, 200...) is the bound column of the combo box.

As for the print button... Again, take a look at Select Case as that may be the way to go..

Without knowing more about your database structure the advice given is 'generic' but should work in your case...as I understand it....

Let us know if this helps or confuses...

Jack

I am beginning to see the light at the end of this tunnel!

I understand exactly what you are saying about limiting the dbase. It is a huge concern of mine as well but for right now the client just wants this step corrected and then we will go forward with correcting the structure.

Thanks for the code advice - it works.

Only issue now is I am unsure as to where to put the stLinkCriteria statement in this code in order to open the form showing only the selected docket....

Jody

Jody -

Is there more than one Docket for each Client? If that is the case then you may want to have two combo boxes on your form where you select the Client and then in the next combo box, which is filtered by the first, you find the Docket you want and open it in the After Update of that combo box:

DoCmd.OpenForm "NameOfFormGoesHere", , , "[DocketID] = " & Me.NameOfDocketComboBox

Without knowing more about your structure this is my best guess on the information that I think I understand...

hth,
Jack

I can't believe I am asking for more help but I am...

There is more then one docket per client. All of the initial search is coming form one table (tblorders).

More details...

Splash screen opens, drop down menu is on it and the data selected is from 1 table (tblOrders)

Drop down shows the JOB NUMBER CLIENT CODE and DESCRIPTION

Table tblOrders sample data

JOB_NUMBER CLIENTCODE DESCRIPTION
123 116 scanning
154 116 keys
8989 334 flyers
939 939 testing

Need it to open the specific client code form AND be linked by docket#, showing only that record.
e.g. Select JOB NUMBER 123 in the drop down and have Form 116 open showing the docket for the scanning job.
Does this make sense?

I tried doing the two drop downs but I couldn't get it to work right - is that because I was using the same table for both?

I am a little confused...and you say the data is coming from a single table... Let's go back to square one and see what your table(s) look like... If you have clients and each Client can have many Dockets then your structure should look something like this:

tblClients
ClientID (PK and auto)
LastName
FirstName
...other fields about the Client...

tblDockets
DocketID (PK and auto)
ClientID (FK)
Description
...other fields related to a Docket...

With this setup you have a form based on tblClients and a subform based on tblDockets. Selection a Client in the main form shows their Dockets in the subform.... That simple...

Not to find a show a specific Docket for a Client you can have a combo box based on tblClient and one based on tbDockets. The first combo filters the second so you see only Dockets for the selected Client. From that combo you can select the Docket you want to see...

Does this help? Does your structure look something like the tables above?

Jack

Ok, I have tried all the advice this weekend and came to the conclusion that the client needs to agree to let me overhaul their current dbase and let me build it properly for them. The way it is set up now is not agreeable to ... well, anything that they are now wanting to set up.

In the meantime I have set up the splash form to allow them to simplify their selection until this gets approved.

The splash screen has:

A drop down search menu
A subform linked showing only the field for the CLIENT ID name (a table was created with the codes and company names and was then linked in to the splash form)
6 buttons to open the data entry screen per individual client.

So, when they search for their docket, the field for CLIENT ID name shows the client name for that docket and they can then select the button for that client. Currently they have to search paper files to know which docket is with which client.

Very basic and silly but it works.

I wonder though if there is a way to have the appropriate client button be highlighted or change color when the CLIENT ID name field is populated...Meaning when they search for the docket in the drop down the appropriate button will flash, or change color or something that will allow me to delete this subform for CLIENT ID name.

Thanks for your patience with me.

Jody

Jody -

We are glad to help!!

If the structure of the database is not normalized then everything you do is merely a stop-gap measure to try to keep the ship afloat until you make things right. Trying to make the db work is only going to get harder so I would suggest you stop spending an awful lot of time trying to get what you have to work and put that energy into going back to square one and normalizing the structure.

As far as your button question.... Hide all of them and then make visible the one you want as that is the easiest way to go...

As far as your client goes...you are the professional programmer so be honest with them. They wouldn't drive a car with a flat tire and it sounds like they are trying to drive this one with more than one flat...

My 3 cents worth and good luck!!! If you have questions you know where to find answers...

Jack

Hi Jack,

I am going back to this issue for a second...I have been doing some overhauling of the dbase and this part is causing a minor glitch...I can get it to open the correct form based on the selection from the combo box but I need it to open the form and ALSO show the correct job_number record that was selected in the combo box as well.

Combo box shows 3 fields: job_number, code_#, job description. (Code_# is the bound column)

Right now my code is:

Private Sub Combo82_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String


Select Case Me.Combo82
Case 117
stDocName = "frmcompany1 cofc"
stLinkCriteria = "[JOB_NUMBER]=" & Me![JOB NUMBER]
DoCmd.OpenForm stDocName, , , stLinkCriteria
etc. etc. etc...

End Select
End Sub

The link criteria is not working and I know it is not the right way to do this so would you have any suggestions for the proper way to do this?

Thanks!

Jody

Jody -

Make the job_number the bound column and you should be good to go...

Good luck!!

Jack

But, in order for the Code # to open the correct client form it has to be the bound column...can I do this in the client form on opening maybe, within the query?

You have lost me... Do you want to open Form ""frmcompany1 cofc"" to a specific Job_Number or a specific Code Number? Whichever it is that is the item you want as your bound column... If you want to open to a specific Code Number then put that as the bound column in the combo box. If you want to open to a specific Job_Number then make that the bound column....

hth,
Jack

I need to open the clients form (frmcompany1 cofc) based on the code in the combo box AND also have the form show the data for that job number.

Below is what would appear in the combo box

JOB NUMBER Code# Description
1234 117 Test sheets
5678 120 Blankets
9012 117 pillows


When the user selects the test sheets job with job number 1234 the "frmcompany1 cofc" (client coded 117) needs to open up and show all data for the job 1234 involving the test sheets.

Does this make sense?

Let me clarify a bit more...
The user needs to pull up a form that is specific to the client (1 of 6) and have the form show information for the specific job that they selected in the combo box.

It sounds like you need a form/subform. The form is based on ClientNumber and the subform shows the selected JobNumber:

DoCmd.OpenForm "frmCompany1 cofc", , ,"[CodeNumber] = " & Me.NameOfComboBox, , , Me.ComboBox.Column(1)

The CodeNumber is the bound column of the combo box and be sure you are not using the "#" in the name of the field.

In the On Open event of the form "frmCompany1 cofc":

If Not IsNull(OpenArgs) Then
Me.JobNumber = OpenArgs
End If

The subform is based on a table/query of JobNumbers with CodeNumbers. On the main form you have a control called JobNumber and that is where the code above will place the JobNumber.

The subform will have these links:

MasterFieldLink - NameOfControlOnMainFormWithCodeNumber;JobNumber
ChildFieldLink - NameOfControlWithCodeNumber;NameOfControlWithJobNumber

I hope I made this clear enough for you to follow...

Jack

I am a little confused...but I will try to work through this a bit and see if I can make sense of it. Thanks!