Save record with default values?..Please

[gusbrunston]

.
I've tried to solve this problem several ways.

There are two fields in the table "tblMyRecords": recordID & dateID

A form based on tblMyRecords with two text boxes: txtRecordID & txtDateID

Both text boxes fill with default data:

txtRecordID = DMax("[recordID]","tblMyRecords")+1
txtDateID   = Date()

What can I do to save a new record with just the default data? ...without entering new data in either field? I've made command buttons, I've copied DoCmd lines into code, I've tried macros, etc. Whenever I close the form, the new record is not saved. (If I enter a number or a date, the new form is saved.)

Please, your help would be greatly appreciated! :-I Gus Brunston
An old PICKer
padregus@home.com

 

[rbowes]

Try this:

Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim strsql As String
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM tblMyRecords Where 1 = 2", dbOpenDynaset)
rs.AddNew
rs.Fields(0) = Me.txtRecordID
rs.Fields(1) = Me.txtRecordDate
rs.Update
Set rs = Nothing
Set db = Nothing
end sub

That seems to work for me each time.

Bob

 

[gusbrunston]

.
Dear Bob:
Thanks for the suggestion. I copied everything verbatim into the onclick event for the save button. No error messages, but it still did not save the record. ??
I guess the user will have to enter the date. That way, the record is saved.

Gus Brunston
An old PICKer
padregus@home.com

 

[japyy]

Bob's coding seems to be OK, but it doesn't requery the form itself. Check if the record is saved in the table!

 

[gusbrunston]

.
Dear Bob and Jappy:

Thank you for reading and responding to my thread. I appreciate your suggestions.

The record is not saved in the table.

To work around my problem, I gave up trying to save the record with default values, and decided to trap a null in one of the fields. Works to an extent--if user tabs past a blank date field, the msg box pops up and asks for a date--but then I can't get the focus back to the date field. I can "ReFocus" to the field before and the field following but not to the field I need to focus on! My code is:

Private Sub txtDepositDate_Exit(Cancel As Integer)
    If Me![txtDepositDate] = "" Or IsNull(Me![txtDepositDate]) Then
        MsgBox "You must enter the date", vbExclamation, "Missing Info"
        Me.txtDepositDate.SetFocus
        Exit Sub
    End If
End Sub

I go from one thing to another that doesn't seem to work for me! There must be some god of Access that I have neglected in my evening sacrifices!

Thanks again.

Gus Brunston
An old PICKer
padregus@home.com

 

[japyy]

Strange, maybe you're using Access 2000? I thought that didn't use DAO, but might be mistaken (and to lazy to check...) I tried Bob's code, and it runs fine in A97. Even simpified the idea to:

Private Sub cmdSave_Click()
    DoCmd.RunSQL "INSERT INTO tblMyRecords ( recordid, recorddate ) SELECT " & Me.txtRecordID & " AS Expr1, #" & Format(Me.txtRecorddate, "mm/dd/yy") & "# AS Expr2;"
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
End Sub

Two other things might bug you:
- the default setting for ID, I used the syntax:

=DMax("recordID";"tblMyRecords")+1

- if you use the same textboxes to show the actual records, your default-settings only work if the focus is actually on a new record...

 

[gusbrunston]

.
Yes, I'm using Access2000.
.
Fingers crossed, I think I've accomplished my first goal...to allow the user to tab through default values when adding a new record. I put procedures in the On enter and in the On Exit events of the date control:

Private Sub txtDepositDate_Enter()
    If Me![txtDepositDate] = "" Or IsNull(Me![txtDepositDate]) Then
        Me.txtDepositDate = Date
    End If
End Sub

(now, just to be sure...)

Private Sub txtDepositDate_Exit(Cancel As Integer)
    If Me![txtDepositDate] = "" Or IsNull(Me![txtDepositDate]) Then
        MsgBox "You must enter the date", vbExclamation, "Missing Info"
        Me!txtDepositDate.SetFocus
        Exit Sub
    End If
End Sub

Evidently, the date field in a new record being null, it accepts the input from the On enter event as if a user were keying it in...and when the command button to save, or the form is closed, the new record is saved in the table. If I were paid $300 dollars for this solution, I'd be earning about $10 an hour (which ain't bad, since I work for minimum wage). .
Gus Brunston
An old PICKer
padregus@home.com

 

[japyy]

Consider it a sacrifice to the gods of Access you mentioned.
Still seems to be a rather compicated way to solve something relatively simple but:
if it works don't fix it...