Default value disappears for prim key field

[azstyx]

Hello,
MS Access 2000 sp3
When adding a new record to an order file, I have a default value on the ordnum field. It also has a format = "9999\-999". The default value is "0603-".
When the user clicks on the new rec nav button, the form has the correct default in the ordnum field "0603-". However, when the user trys to key in the remaining 3 digit sequence number (.i.e. result 0603-123), the default value disappears as soon as the use startes to type the first character. The table source is a query that links the order table to the customer table via cust num. When i use only the order table as the form source, this works ok, no issues but i see this issue when i am using a query joining two tables.

SQL=SELECT tblOrders.ordnum, tblOrders.orddate, tblOrders.salesm, tblOrders.custnum, tblOrders.comments, tblCust.*
FROM tblCust INNER JOIN tblOrders ON tblCust.custnum = tblOrders.custnum;

Does anyone have any ideas why or how I can correct this?
Thanks!!

 

[ProgramError]

Use the Input mask not the default value property.

try "0603-"999



Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.

 

[azstyx]

hmm, tried that but got no value in the ordnum field on a new rec. What I need is:
click on new rec button
Ordnum field should display [0603- ] then the user will enter a 3 digit seq number (such as 123) after the "-" giving the result: [0603-123]...that is the key value that should be stored with the new rec.

 

[BoxHead]

azstyx,

I once got something similar to work by using the format property of the table set to "FormXXX" to grab the name of the form that was being input. HOWEVER, I learned that while the form name would appear in the table itself, I couldn't query by the form name because it was not actually part of the data.

I'd suggest putting two unbound textboxes on your form. txt1 set to = "0603-" and txt2 left blank. Disable and lock txt1 as well as the textbox that is bound to your order number field.

On the AfterUpdate event of txt2 use

YourOrderNumberTextBoxName = txt1 & txt2

Now IF the leading value of the Order Number represents the Year 2006 and the Month of March and you plan on changing it each month, then you would be saving yourself a world of hurt (in the form of Outlook Reminders and "I-couldn't-enter-anything-this-morning-because-the-month-is-wrong" whining) by using the same two textboxes and the same code, but set txt1 to
= "Format(Year(Date()),"00") & Format(Month(Date()),"00") & "-"
That will set the leading value to the current year and month with a dash.


HTH


John






When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[azstyx]

Actually my default value is:
=Right(Year(Now()),2) & IIf(Month(Now())<10,"0" & Month(Now()),Month(Now())) & "-"
I just simplified it for purposes of demostrating the main issue of the default value clearing on me.
I'll play around using two text boxes.
thx,
styx