Problem with Synchronization of Two Combo Boxes (1 Viewer)

[Pathetique]

I have a very specific problem with synchronizing two combo boxes on a form. The form name is "Time Book" and contains about 8 fields (all required), of which only two are combo boxes. Their names are "Job ID Code" and "Job Name". Currently, the user picks a "Job ID Code" and the choices in the "Job Name" box are automatically limited to the ONE job name that is associated with that unique job id code (a fairly common form technique). These associations are in a table called "Work Orders". The form writes to a table appropriately called "Time Book". Below I've listed some pertinent properties for the combos:


For the "Job ID Code" combo:
Row Source Type: Table/Query
Row Source: SELECT [Work Order].[Job ID Code] FROM [Work Order];

For the "Job Name" combo:
Row Source Type: Table/Query
Row Source: SELECT DISTINCT [Assigned Job ID Codes Query].[Job Name] FROM [Time Book], [Assigned Job ID Codes Query] WHERE ((([Assigned Job ID Codes Query].[Job ID Code])=[Forms]![Time Book]![Job ID Code]));


Here is the problematic scenario:
A user chooses a Job ID Code, then selects the Job Name. Everythings cool. However, the user then realizes they meant to choose a different Job ID Code, so they choose a different one. Everything's still cool. Then, without checking, the user (having already selected the previously associated Job Name) doesn't select the newly associated Job Name, and proceeds through the form, tabbing through to the point where the record is saved and the form is reset, ready for another record to be entered. Everything appears cool (to the user). But, in the table the incorrect Job Name appears, the one that was originally selected.


I've tried the Limit to List property, I've made a requery macro and put it in seemingly every possible event, and numerous other schemes that I can't remember right now. But, nothing prevents the problem. You can tell that it is doing it by choosing an item from combo1 and choosing a corresponding item from combo2, then changing combo1, checking the drop down menu in combo2, and then changing combo1 back to your original choice, and finally noticing that the old item from combo2 is still selected.

The only thing I can come up with for a solution is maybe I could create a default selection for the "Job Name" combo, so that when the form is requeried or reset, the selection for that field is also reset. But as of yet, I cannot figure out how to force a default selection on a combo box that gets its values from a table. What have you datamasters?

Path

 

[Dave Eyley]

How about using the OnGotFocus on the JobId combo to clear the JobName combo so that re-entry is required again.

It doesn't matter when a user enters the JobId the first time around since JobName is Null anyway, but if they return to the JobID and change it then it Nulls the JobName and problem solved....


So,

In the ONGotFocus property of the JobId select Code and enter the following bit of code...

Me!JobName=Null

Will that work?

Dave Eyley

 

[Pat Hartman]

If there is one and only one JobName for a JobID, you should NOT be storing the JobName in the TimeBook table. You would retrieve JobName whenever you need it by joining to the WorkOrder table.

To make autolookup work in your situation, remove the second combo and replace it with a text box. Set the textbox's locked property to Yes since you don't want to accidentally change the JobName on this form. Change the form's query to join to the WorkOrder table on JobID. Select the JobName in addition to the other fields already there. Remove any code from the JobID combo's events. Open the form in design view and delete the reference to the recordsource, save the form, and re-add the query name as the recordsource. Bind the controlsource of the new textbox to the JobName field in the recordsource.

When you choose a JobID from the combo, the JobName will automatically be populated.

 

[Pathetique]

I'm not sure I know how to join the JobName from the WorkOrder table to the JobName field in the TimeBook table. I understand the reasons for doing this, as it would eliminate possible errors in the field, but I'm just not sure how. Pat, do you think you could explain the process? And, if I could ask one more related question concerning relationships; how are they most commonly used? I've tried linking the JobName from the WorkOrder table to the Job Name in the TimeBook table using a one-to-many relationship, but I'm not sure what it does. I really appreciate the help, as I'm new to Access. Also, Dave, thanks for your suggestion but I couldn't get it to work correctly. Where exactly should I put the bit of code?

Thanks again

 

[Dave Eyley]

I would agree with Pat. If there's only one selection of JobName for a given JobId, there's no point in having the second combo.

Also, why is JobName in both tables when JobId is there as the linking field?

Relationships are the core of these databases. Get them right and they work well. Get them wrong and it's a case of staying up all night wondering why the damn thing doesn't work

If you are using Access 97, look in the help files index under -

Relationships, database design. It explains about why table design is so important.

I think when you've read that and a few other bits of the help topic you will be 'all knowing'.

Good Luck...

Dave E

 

[Pathetique]

The job name is important only so that users can see that they have put in the code that they meant to add. It's more of a mental reference to them than anything. The code is just not "memorable" to them, but is very useful for others.

How would I retrieve the Job Name from the Work Orders table so that the Job Name appears in the Time Book table. Although there is one ID for each Job Name in the Work Order table, there are numerous records in the Time Book table for the same Job Name/ID. This is where I thought the one-to-many relationship would automatically fill-in the Job Names, but it doesn't really seem to work. The Job ID Code in the Work Orders table is the primary key.

path

 

[Dave Eyley]

Path, (odd name, but I guess you know where you're going- ;-)

On the form where you want the data displayed use a crosstab query to combine the tables and show the fields you want. You could delete the JobName from the WorkOrders table, but still have it on the form - taken from the Timebook table in the query.

The tables would be linked by the primary field JobId.

I hope this makes sense.

Dave Eyley

 

[Pathetique]

I thought I had a good handle on all of this, but I'm really lost now. I've changed the Job Name control on the form (the form's name is "Time Book") to a text box and I just want to use AutoLookup to get the name based on what the user chooses in the previous combo box (which is called Job ID Code). The form's record source is the Time Book table (where I want to write to) and the place that has the Job Name/Job ID Code associations is a table called "Work Orders". Where do I go from here? If I can just get through this I think it'll meet my needs.

taken a wrong turn,
Path

 

[Pathetique]

OK, so I forgot an equals sign. Big deal. (I forgot an equals sign in the text box that gets retrieves the job name from a query created in the combo box.) I knew I was writing it right, but it kept returning an error. Anyway, now that my text box displays the Job Name that corresponds to the chosen Job ID Code, I need it to actually write the value to the Time Book table. In the Time Book table the Job Name field is required, and when you fill out the form the Job Name field works (like I just described) but doesn't seem to write anything and when the form tries to save the record to the Time Book table I get an error that says:

The field 'Time Book.Job Name' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.

How do I get the table's Job Name field to associate with the contents of the Job Name text box in the form, or rather, how can I get the contents in the Job Name text box of each new record on the form to write to the Job Name field of the Time Book table? And, really, I don't even need to do this if I could just figure out how to make the Job Name field in the table automatically correspond to the correct Job ID Code field (which is also a field in the Time Book table, but which is assigned in a Work Orders table). Since there is just one Job ID Code for each Job Name, what I would like to happen is that the user puts in a work order where they assign a job id code to a job name. Then, when a person needs to enter time for this job they go to the Time Book form, where they put in the Job ID Code and some details about what they did, etc. The Job Name text box simply displays the Job Name so the user gets an affirmation that he entered the correct Job ID Code. When the record is added to the Time Book table, I need the Job ID Code and the Job Name to be displayed since it is difficult to filter the records using Job ID Codes (that is, if you don't know the Code). It is at this point that I am having problems. I want the table to get the right Job Name from the work orders table like I explained above. I really hope this makes sense. It seems like a very simple goal, but I must be missing something.

path

 

[Dave Eyley]

Path,

Can you copy the database, remove all but a few records in each table (to cut down on transfer time), compact it and post the database, either to the forum or to my email address?

'I think it's time (the Walrus said...)' to have a look at the forms and work it out from there. As long as it's Access 97 and not 2000.

If I can, I will attempt to get it working according to your instructions so far and post it back.

My email address is on the forum but here it is anyway....

daveeyley@lineone.net

Dave Eyley

 

[Pathetique]

I'm sad to say that I'm using Access 2000. I tried to use the conversion tool to convert the database into an Access 97 file, but it said that there were errors that prevented the conversion. So, I don't know what to do. Any suggestions? I can post any code you need.

 

[Dave Eyley]

OK - plan B

I've saved the page for printing and I'm going away to read it thru quietly and see if I can't solve this one. I don't give up easily.

I'll be back - I'll bet it's easy really!!!!!!!

 

[Pathetique]

Dave,
I think I've figured out a solution. I made a query with all the fields from the 2 tables that I need. Evidently Access "knows" that the records go together. So, instead of the users seeing the time book table I'll just let them see and print this new query. I can leave the Job Name field out of the Time Book table completely (just as Pat said). I can still have the text box in the Time Book form so that users can see that they are putting in the right code. Everything works ok so far. Man, I owe you guys and the whole board. I look around here all the time and 99% of the time I find a solution. Dave, I hope you get this message before taking time to look for a solution. I do have one final question, and it is more of a general recommendation request. Is it bad to use spaces when naming objects, fields, names, etc. I would like to remove them and rename many of my forms, tables, etc., but I'm afraid of what will happen to all the things that reference those objects. Does Access "know" when you rename something and change all the references to that something. I should probably ask this in a new thread and probably in the general forum, but what the hay?

Path

 

[Dave Eyley]

Congratulations!

It's always better finding the solution yourself in the end - much more satisfaction!

Spaces in any access names is bad practice. But now that you've got them, just ensure that you always put them inside [] brackets before using them in code.
Changing them now will give you problems since Access does not recognise the new names.

I had come back to give you, what I thought was, a solution but what the heck! You got there!

Just a few suggestions though -

1. I always find it less complicated to use all unbound fields on input forms. This way you can check for errors and omissions in the entry before they get to the table.

2. Get your head around reltionships. They are very important to the basic structure of Access databases. There are good books around, probably in the library that cover the subject well.

3. Keep it as simple as poss. Don't duplicate fields unecessarily in tables. Only Primary links should be the same so that Access can see a relationship.

e.g.

2 tables

The 1st table has an author's name, DOB, number of books written, biography etc... as fields. plus a primary field (this could be an autonumber or just a field with the contents unique to that author.

The second table contains all the books this author has written, the dates, the publisher, ISDN No, etc but again has the same unique field in each record as table 1.

In relationships you join the two tables by dragging the one unique field onto the other and creating a relationship.
From this point on Access knows that the data in Table 2 is related to the data in Table 1 via the unique field.

The relationship window that opens when you do the dragging allows you to create the join and asks if you want cascading update or cascading delete. This simply means - do you want changes in Table 1 to be reflected in Table 2, i.e. if you delete an author's record from Table 1, do you want it to delete all associated (or related) records in Table 2.

Hope this is of some help - it won't substitute for a good book, though.

Good Luck with the database.

Dave Eyley