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

[hannoudw]

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 ??

[FishVal]

The obvious reason is that no record has been created in main form and thus no record has been saved into Invoice table.

[hannoudw]

But it's a bound text box and it must save automatically !!

[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.

[hannoudw]

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?

[munkee]

Edit: ignore this post im totally confused

[hannoudw]

what do you mean by that ??

[mshmyob]

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,

[hannoudw]

Yes You got a point .
Invoice (Invoice_Num, Invoice_Date, TotalPrice_Befo re_Discount, Total_Paid, Total_Discount)
Invoice_Details (Invoice_Detail_ num, Article, size, Invoice_Num, quantity, price, Discount, Location)

[mshmyob]

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,

[FishVal]

The following code snippet will force new record creation and saving:

    With Me
        .fld1.SetFocus  'the following line will fail unless bound modifiable control has focus
        .Dirty = True
        .Dirty = False
    End With

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.

[hannoudw]

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??