By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,574 Members | 1,199 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,574 IT Pros & Developers. It's quick & easy.

The primary key wont save in the table when i set the default value of the date

100+
P: 115
Hi I have a form that contains 2 text boxes and a subform contain the Invoice details.
the 1st text box is txtInvoice and its bound to Invoice_number in the invoice table , it's an auto-number. and the 2nd text box is the txtDate it's bound to the Invoice date in the Invoice table.
the subform is related to the main form by the Invoice number (txtInvoice).
The problem is when i set the default value of the txtDate as the sysdate (=date()) , each record of the invoice detail will be saved in the Invoice_detail table without the txtInvoice (without the Invoice number!!).
And in the table Invoice the record will not be saved .
But if i don't put the default value of the txtDate= date()
all the records will save correctly in both tables , with the Invoice number .
Did any one know why ??
Aug 15 '10 #1

✓ answered by FishVal

It won't be saved until you make change to any field of a record. Since autonumber field is not editable and date field already "shows" right value I guess no changes are made and no record is being created.

Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
The obvious reason is that no record has been created in main form and thus no record has been saved into Invoice table.
Aug 15 '10 #2

100+
P: 115
But it's a bound text box and it must save automatically !!
Aug 15 '10 #3

FishVal
Expert 2.5K+
P: 2,653
It won't be saved until you make change to any field of a record. Since autonumber field is not editable and date field already "shows" right value I guess no changes are made and no record is being created.
Aug 15 '10 #4

100+
P: 115
So what can i do in this situation?
I just want to when i open the form to not fill the date , but i want it to be saved in the database ! how can i do it? should i keep the default value of the date or what?
Aug 15 '10 #5

100+
P: 374
Edit: ignore this post im totally confused
Aug 15 '10 #6

100+
P: 115
what do you mean by that ??
Aug 15 '10 #7

mshmyob
Expert 100+
P: 903
Fish is correct that when using an autonumber you need to actually make a change to a field before the autonumber is commited.

I am curious about your table design between tblInvoice and tblDetails. Could you show us the table design for these two tables, then we may be able to help further.

Also my 2 cents on using an autonumber as your invoice number for an invoicing app: That is a big no no. All invoices MUST be accountable, for audit purposes and that will be impossible using an autonumber. ie: All invoice numbers must be sequential with no missing numbers. Since this is almost impossible with autonumbers you should consider using a system that either allows manual input of invoice numbers with coded sequential checking or an automatic coded system that increments to the next available invoice number.

cheers,
Aug 15 '10 #8

100+
P: 115
Yes You got a point .
Invoice (Invoice_Num, Invoice_Date, TotalPrice_Before_Discount, Total_Paid, Total_Discount)
Invoice_Details (Invoice_Detail_num, Article, size, Invoice_Num, quantity, price, Discount, Location)
Aug 15 '10 #9

mshmyob
Expert 100+
P: 903
Looks fine assuming Invoice_Detail_Num is an autonumber.

So your question has been answered by Fish and I would still suggest you stop using an autonumber for the Invoice Number.

Oh an as I pointed out in another post the word "Size" is a reserved key word and should not be used for a field name. Remember to prefix ALL field names with an 3 character designation indicating the data type of the field.

I also notice you are storing totals in your invoice table - that is also not recommended.

cheers,
Aug 15 '10 #10

FishVal
Expert 2.5K+
P: 2,653
The following code snippet will force new record creation and saving:
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         .fld1.SetFocus  'the following line will fail unless bound modifiable control has focus
  3.         .Dirty = True
  4.         .Dirty = False
  5.     End With
  6.  
Mshmyob has made a good point about invoice numbering mechanism. However, be aware that you should take a special measures to prevent invoice id duplication when number of simultaneous users is >1.
Aug 16 '10 #11

100+
P: 115
Ok thanks I'm working on it .
I made the Invoice number (Number) instead of auto number.
And now instead of a continuous bound form, i'm working with an unbound form , and every time that i load the form i will calculate the next Invoice number . I will let you know the results .
Do you thing guys that it's better this way ?
Or do you suggest a better way to do the Invoice form??
Aug 16 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.