Data Entry Problem

[janetlyn]

I have two tables: TBL-Invoices and TBL-Hours. Both have the same primary key of ProjNo.

I have two forms: FRM-Invoices and FRM-Hours to input the above information into the tables. If someone is inputting into one of these tables and it gives an error message of referential integrity like "the projno is not in the main table so I cannot put into the child table".

Knowing this might happen there is a button to click that will have them leave the record they were working on, and go to the form for the FRM-ProjectDetails to input the new project and all its information.

If a person then starts putting in the project information and they come to a field with a drop-down list; and their (project name, client name, AR client name, CityState, all drop-down boxes in this form) is not in the drop-down list, they must leave the record they are inputting, (this is the second record they are leaving mid-input and go to another form such as FRM-CityState.

Here is one of the problems. Once I go to the third form, enter my new CityState, and then return to my second input form FRM-ProjectDetail, drop down the box to input the CityState it does not show up in the drop-down. I have to close the FRM-ProjectDetail and then reopen for it to appear. However, when I do this it adds the new project number to one of two tables in a query. But when I go to look for that project number in the form, it is not available to find. I have to go open the table and delete the project number, go back to my form and start over. Once that is corrected and I exit it leads me back to my original form I was inputting on and allows me to then save the record and go on.

I changed it so someone could type in a new "citystate" but it was not adding that to the TBL-Citystate. So, that won't work either; plus I did not want someone typing in wrong junk anyway. In one form I have a delete key so decided before I could go on to the second and third input screens I should delete the problem record go add my new information and come back and re-start the record. This worked fine unless again, I messed up referential integrity.

I was going to put in a little code so when I went from FRM-ProjectDetail to FRM-Citystate; add my new citystate, and then "Close" it would close and then reopen FRM-ProjectDetail, thus putting the new entry in the drop down box. But, this caused my other problem of then it not allowing me to put in a duplicate project number, but the project number is not to be found either since it is in only one of two tables in a query the form is referencing.

Can anyone think of a better way to handle this problem? I feel like I am in a conumdrum (can't spell it either).

I would appreciate any help offered. Janet Lyn

 

[Rolliee]

If the two tables have the same id and must start at the same time, why are there two tables? Be sure it gains something before you try a work-around. A workaround might be open t wo records, one on each table at the same time but that seems self defeating. Maybe I just do no t
understand.

Rollie@bwsys.

 

[janetlyn]

One table holds the new project number (Main Table), name of project, description, and whether it is active. Other table holds specific information about the project; like year, who is our paying client, who is our working client, the type of job (healthcare, industrial, educational), how the job is billed (hourly, max fee, percentage), and how many observation hours are in the contract.

However, when entering the data through a form, I thought it would easier to just use one form to input all the information. So I did a query combining all the fields from both tables. However, you can only have one field name named the same (ProjectNo) so I have the ProjectNo from the Main Table in the query.

Did that clarify or just confuse some more? Thanks for your help Rollie. JL

 

[fkegley]

So the two tables involved are in a one-to-one relationship? I agree with Rolliee, it will be MUCH easier to put all of the data relating to a project in the SAME table. You can avoid the problems with Referential Integrity, avoid opening another form to input data. As for city/state entries, if you have a notion what they will be, give them combo boxes to pick from (as you are apparently doing), otherwise, I would let them type in the City name and select State Code from combo box. You may also want to look into the Requery method of a combo box.

Frank kegley
fkegley@hotmail.com

 

[janetlyn]

So if I have two tables with a one-to-one relationship (which actually I have 8 tables with a one-to-one; all based on the ProjNo), I have to have one form for each table to input information? Or, combine all my 8 tables into 1 table?

I have not heard of the Requery, and I think I am using List boxes, but I will look into the difference between the List and Combo and how to requery and see if that helps.

Appreciate your help Frank, JL