Default values in forms

[mveijndh]

Hi,

In my effort to reduce the input for my user as much as possible, I'd like to use as much default values as possible. Now I want to set the default value for a field to the last record of a table. How can I do this on the form. I assume the code has to run when the the form opens or updates, I can find that event, but what would be the best way to do this?
Further more this event only has to run when a new record is added to the table and should not change any more!!

Thanks in advance!

 

[Uncle Gizmo]

That happens automatically, depending on the table index, or query sort order.

 

[mveijndh]

Hi, I might have been not exactly clear. I'm filling fields in the tblOrder by a form, frmOrderAdd. One of the fields I need later is the excDefaultID. this guides me to the current exchange rates defaults. I don't want to fill in the ID number, nor do I want to select this in a drop down, I just want to have it filled in, but it has to be the latest record in the table.
I've tried to sort it this way:

Private Sub Form_Current()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lgExcDefaultID As Long
Dim strSQL1 As String
strSQL1 = "SELECT tblExcDefault.excChangeAutoID " & _
"FROM tblExcDefault " & _
"ORDER BY tblExcDefault.excChangeAutoID DESC;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL1)
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
lgExcDefaultID = rst!excChangeAutoID
End If
Me!OrderExcDefaultID.Value = lgExcDefaultID

Set dbs = Nothing
Set rst = Nothing
End Sub

But now every time I cycle the records, it changes the default value and I don't want that, I want this only to happen on a new record. Does this mean I need a seperate form for entry and one for updates or is there a parameter available that if this is a NEW record it runs only the code?
I assume there are better solutions??

 

[Uncle Gizmo]

How did you get on with the other question I answered for you recently?

Here:-

http://www.access-programmers.co.uk/forums/showthread.php?t=283537

 

[Uncle Gizmo]

MS Access provides a function which tells you if it's a new record and I think the function is named new record! However I'm not sat at my PC at the moment so I can't check.

I think the code to check if it's a new record would be something like:- If Me.NewRecord Then.....

 

[Uncle Gizmo]

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst

I'm wondering what would happen if you replaced:-

rst.MoveFirst

With

rst.MoveLast

?

 

[arnelgp]

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim ctrl As Access.Control
    Set rs = Me.RecordsetClone
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            For Each ctrl In Me.Controls
                If TypeOf ctrl Is Access.TextBox Or _
                    TypeOf ctrl Is Access.ComboBox Or _
                    TypeOf ctrl Is Access.CheckBox Then
                    If .Fields(ctrl.Name).Attributes And dbAutoIncrField Then
                    Else
                        ctrl.Value = .Fields(ctrl.Name).Value
                    End If
                End If
            Next
        End If
    End With
    rs.Close
    Set rs = Nothing
    SendKeys "{ESC}"
End Sub

 

[mveijndh]

Hi Gizmo,

Indeed it is. I've implemented it! Thanks!

 

[arnelgp]

do your code in the form's Before Insert event.

 

[Uncle Gizmo]

    Set rs = Nothing
    SendKeys "{ESC}"
End Sub

From years back, I recall the general advice was not to use send keys? Has anything changed with recent versions of MS Access?

 

[mveijndh]

How can I declare a Thread as SOLVED?

 

[Uncle Gizmo]

I marked it solved.

At the top of the thread there are 4 buttons on the right. One of them is "Thread Tools" the option to mark the thread solved is under there, well on my view. I may have more options than you seeing as I am a moderator. I would be interested to know if you can see the option to Mark the thread solved?

 

[mveijndh]

Thanks, That worked!