473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

115 100+
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.

11 2136
FishVal
2,653 Expert 2GB
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
hannoudw
115 100+
But it's a bound text box and it must save automatically !!
Aug 15 '10 #3
FishVal
2,653 Expert 2GB
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
hannoudw
115 100+
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
munkee
374 256MB
Edit: ignore this post im totally confused
Aug 15 '10 #6
hannoudw
115 100+
what do you mean by that ??
Aug 15 '10 #7
mshmyob
904 Expert 512MB
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
hannoudw
115 100+
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
904 Expert 512MB
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
2,653 Expert 2GB
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
hannoudw
115 100+
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

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

Similar topics

3
by: Ian D | last post by:
Firstly apologies for the convoluted question. I found this problem whilst building a larger database. I've distilled it down to as small as possible and can send a 200k example to anyone who has...
2
by: hyphen | last post by:
I have set up two date fields in a table: 1stpromisedDate and CurentPromisedDate The first field is to record the 1st promised delivery date of a supplier and the second is to record the...
5
by: DSmith | last post by:
Is it possible to make whatever is inputted the default value of a field until a different value is inputted? Thanks in advance!
12
by: Emi Lu | last post by:
Hello all, I have a question about "date" & "timestamp" types in PostgreSQL. I want to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for them. However, it seems that PostgreSQL...
5
by: Øyvind Isaksen | last post by:
I have a page with an optional integer-field, and one asp:calendar control. I use a stored procedure to save the data in SQL Server. When all fields contains data, the code works great! But if the...
3
by: binder | last post by:
I am designing a new table with a few columns that may or may not have a value on each row that is inserted. What issues determine whether to allow a NULL value to be inserted for that column or...
3
by: zufie | last post by:
I want to use SQL code to Create a Table & Provide a Default Value for Each Column. Here is the code I am using: CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address...
4
by: rczuba | last post by:
Problem: Creating a Default Value for a field in a subform when a field in the subform & form match. I'm trying to create a payroll database for a small home business that I have that has had...
2
by: ZEHAVISHUL | last post by:
I create table with default value, How can I show the default value. desc'TABLE' doesn't show the defualt value. thank zehava
4
by: billa856 | last post by:
Hi, I want to know how can we set the value of Textbox = value of field in table when we select a value form combobx. example i have a table customer CID CNAME CSALARY 1 Billa ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.