By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Help with Access/VBscript

P: 6
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
Sep 7 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
'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
Sep 7 '06 #2

P: 6
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
Sep 7 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Sep 7 '06 #4

P: 6
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
Sep 7 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Sep 7 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.