How do you stop a new record being added to a table?

DRM54

New member
Local time
Today, 13:04
Joined
Jan 1, 2010
Messages
4
Hello and Happy New Year,
A few years ago I managed to cobble together a VB6 program using unrelated Access tables to record the time spent on various jobs during the working day, allocating this time to each job and using this information to produce invoices.
Having recently upgraded to Access 2007 I am trying to relate my tables and use bound Access forms instead of VB6.
What I thought would be one of the easier tasks has proved to be the most difficult. This is the form to record the Diary Entries and is based on the following.
Any help, advice or pointers would be very welcome.
 
Last edited:
Welcome to the forum.

I'm not sure sure if I fully understand what you are doing and asking but here goes.

I'm guessing your main form is for entering a Diary Entry record. But you are saying that when the user selects a job description from the drop down, this creates a new record in the Jobs table? I assume the query you have provided is the source for the form? I think this is where the problem lies and you can probably demonstrate this to yourself by opening the query and entering data directly. You will have a blank line for a new Diary Entry and you can enter data for that. But if you then type anything into the job description, the query will treat this as a new record in the Job table.

You really don't need such a complex query for your form. Or you must avoid allowing the user to put entries in fields that are not from the Diary Entry table. In the case of the Job ID, just set up your combo to be bound to the JobID field in the Diary Entry table. You can still set up the combo to display the description even though it is bound to the ID.

I don't understand why you have written code to update the Job ID text box on the form. What is the purpose of this? I'm guessin it's because your combo isn't bound.

If my explanation is poor, please upload your database I'll amend.

hth
Chris
 
Looks genuine to me but then I'm gullible :rolleyes:

Thank you for your helpful reply Chris, I want to assure you that it is not any kind of spam whatsoever.

I have used part of my personal website (its primary use is to record our groups golf scores) to make my problem as clear as possible. I hope I haven’t offended anyone by doing so (is this why spam has been mentioned?). This application (if I ever finish it) will be used only in my own one man business.

I have spent many frustrated days on this problem, using bound and unbound forms and bound and unbound controls, but always based on the DiaryEntriesQ query. It now seems, thanks to your input, this is where the problem lies.

I will checkout what you have suggested and report back, if that is Okay with you.

Many thanks and regards,

David
 
Hi Chris,


Your guess is correct, the main form is for entering Diary Entry records, but it also shows the selected customer or allows a new customer to be added and displayed (this seems to work as expected).


When adding a new row in datasheet view of DairyEntriesQ (New) is displayed under DairyID, JobID is blank, if I type an existing CustCode, CustName through to TelNum are automatically filled in, if I type a non existing CustCode these fields are left blank. If I leave the EntryDate field blank, DiaryID and JobID remain blank when I move to the next row. If I then switch to design view and back to datasheet view, the record with the blank DiaryID and blank JobID has been removed, but when I check the Jobs table, this record has been written there.


The idea behind the JobDescription Combo Box is to be able to select an existing current job (one that hasn’t already been invoiced) to allocate the time to or if there is no existing job then create one.


The JobID text box has been unbound in an attempt to get round this problem. By selecting a previous job for this customer from the JobDescription combo list I can put the correct number in the JobID text box, but this doesn’t solve the new row problem in the Jobs table. The JobDescription combo box is currently bound to the DiaryEntriesQ query.


Is the alternative to use a subform for the Job records and link all the subforms directly to their tables?


Am I trying to achieve the unachievable?


Many thanks and regards,


David
 

Users who are viewing this thread

Back
Top Bottom