Help with code

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

Can someone help me with this code. I would like a new case note to be made
when the user clicks on the button. However the code I have meshed together
keeps bringing up the first record instead of a brand new record.

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
 
Hi Emma,

Why not use an autonumber field? Assuming there is a good reason for
not using an autonumber field, why not just go to a new record, assign the
appropriate values to the ID and Case Worker fields on your form and do an
immediate save?

Private Sub newrcd_Click()

DoCmd.RunCommand acCmdRecordsGoToNew
[ID].Value = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1
[Case Worker].Value = CurrentUser
DoCmd.RunCommand acCmdSaveRecord

End Sub

Add appropriate error handling in case of a clash of ID numbers.

Hope that helps,

Clifford Bass
 
Hi,

On my notebook i have over 40,000 songs , on my pc on network ik can see my
laptop and in
media player i can see my notebook , but on network i'm clicking on
PC_MC_VIZAR and it asks
for a Username and a Password , what do i need to type in username/password
?

Can anyone help me?


Thnks!
 
Hi,

This is a discussion group for Microsoft Access, part of Microsoft
Office. You may wish to repost your question in the
microsoft.public.windowsmedia.player discussion group. Someone there should
be able to help you.

Clifford Bass
 
Back
Top