Help with Access/VBscript

[Wilkesy]

Hi All,

Im trying to finish off some coursework im doing. Ive finished the database, created all my forms and everything works perfectly, that was until i created a switchboard!!!

Im using the following code to notify users that when they enter an artist's name (im building a cd database) a message pops up and tells them the artist is already created and then takes them to the relevant record. It works perfectly if i just open the form, but when i open through the switchboard it bugs out!! Can anyone help? The line in bold is where it errors.

Private Sub Artist_Nme_BeforeUpdate(Cancel As Integer)

Dim ArtistID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

ArtistID = Me.[Artist Nme].Value
stLinkCriteria = "[Artist Nme]=" & "'" & ArtistID & "'"

'Check Artist table for duplicate Artist Name
If DCount("[Artist Nme]", "Artist", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Artist " _
& ArtistID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Thanks for your help!

Paul

[MMcCarthy]

'Undo duplicate entry
Me.Undo

You have undone your entry therefore the Criteria is no longer valid. There is no artist on the form to compar to.

Hi All,

Im trying to finish off some coursework im doing. Ive finished the database, created all my forms and everything works perfectly, that was until i created a switchboard!!!

Im using the following code to notify users that when they enter an artist's name (im building a cd database) a message pops up and tells them the artist is already created and then takes them to the relevant record. It works perfectly if i just open the form, but when i open through the switchboard it bugs out!! Can anyone help? The line in bold is where it errors.

Private Sub Artist_Nme_BeforeUpdate(Cancel As Integer)

Dim ArtistID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

ArtistID = Me.[Artist Nme].Value
stLinkCriteria = "[Artist Nme]=" & "'" & ArtistID & "'"

'Check Artist table for duplicate Artist Name
If DCount("[Artist Nme]", "Artist", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Artist " _
& ArtistID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Thanks for your help!

Paul

[Wilkesy]

Hi,
Thanks for the quick reply. Ive tried removong that line before but i still get the same error.
The theory behind using that line (Im by no way an expert) is that i want to undo the data ive just put into that field, and remove the record ive just tried to create, as im bringing back existing data from the table.

Cant seem to get it to work, but its only happening when opening the form through the switchboard.

Cheers

Paul

[MMcCarthy]

What exactly is the error message you are getting?

Hi,
Thanks for the quick reply. Ive tried removong that line before but i still get the same error.
The theory behind using that line (Im by no way an expert) is that i want to undo the data ive just put into that field, and remove the record ive just tried to create, as im bringing back existing data from the table.

Cant seem to get it to work, but its only happening when opening the form through the switchboard.

Cheers

Paul

[Wilkesy]

The Error message is

Run-time error '3021';

No current record

Just a thought, would it be to do with the fact im opening the for in 'Add Mode'??

Cheers

[MMcCarthy]

Once you undo a record you no longer have any values so anything that uses the Me. reference won't see the intended value. Put these values into variables before you undo and use the variables to reference your code thereafter.

The Error message is

Run-time error '3021';

No current record

Just a thought, would it be to do with the fact im opening the for in 'Add Mode'??

Cheers