UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Runtime Error 2105 And Other Vba Problems    
 
   
Skimmer
post Jun 2 2011, 06:07 PM
Post #1

UtterAccess Member
Posts: 39



Hi there,
I have two errors within one code I need help with in VBA for Access 2007.
I have set up a form to cycle on the current record only, requiring the user to push a button to add an item to table.
The following is the code I have used along with notes of explanation and notes in this email showing where the problems seem to be (word of warning: I'm new to all of this and there has been a lot of trial and error along the way so sorry if it is a bit of a mess to read):
Private Sub CmdAdd_Click()
Dim Response As String
Dim SKU As String
Dim LinkCriteria As String
'If "Add Record" button (CmdAdd) is clicked and any fields have been left blank:
If Len(Me.SKU_Number.Value & vbNullString) = 0 _
FOr Len(Me.SKU_Description & vbNullString) = 0 _
Or Len(Me.SKU_UPC & vbNullString) = 0 _
Or Len(Me.cmbComponent & vbNullString) = 0 _
And Me.Dirty = False Then 'Give warning message: (NOTE A see below)
GoTo Message
Else 'Set up variables
SKU = Me.SKU_Number.Value
LinkCriteria = "[SKU_Number]=" & "'" & SKU & "'"
End If
'make sure record does not already exist
If DCount("SKU_Number", "tbl_FG_SKUs", LinkCriteria) > 0 _
Then
Response = MsgBox("This component already exists.", vbInformation, "Record Already Exists")
Else
Response = MsgBox("You are about to add a Component to the list. Please review details in the box below." & vbCrLf & vbCrLf & "Click YES to add record. Click NO to revise.", vbYesNo, "Warning")
End If
If Response = vbYes Then
DoCmd.GoToRecord , , acNewRec (NOTE B see below)
Message:
MsgBox "You must complete all information before adding a record.", vbInformation, "Error"
Me.SKU_Number.SetFocus
End If

End Sub
NOTE A: I have successfully applied this code to other forms, and the new item is added without problem and a new record is displayed, but the message "You must complete all information before adding a record." shows up (I assume because the new record is blank). I'm not sure how to alleviate this problem - I put the me.dirty=false in there as an attempt but to no avail.
NOTE B: All scenarios seem to run fine, but it won't allow me to add the record or to navigate to newrecord. I get a runtime error 2105, you can't go to specified record. DoCmd.GoToRecord , , acNewRec is where this problem is occurring.
Any ideas out there?
Thanks so much,
Kim
Go to the top of the page
 
+
Alan_G
post Jun 2 2011, 06:42 PM
Post #2

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,195
From: Devon UK



Hi
little bit of 'rearranging' should do it, and the Response datatype would be an integer -
CODE
Private Sub CmdAdd_Click()
Dim Response As Integer
Dim SKU As String
Dim LinkCriteria As String
If Len(Me.SKU_Number.Value & vbNullString) = 0 _
   Or Len(Me.SKU_Description & vbNullString) = 0 _
   Or Len(Me.SKU_UPC & vbNullString) = 0 _
   Or Len(Me.cmbComponent & vbNullString) = 0 Then 'Give warning message: (NOTE A see below)
   MsgBox "You must complete all information before adding a record.", vbOKOnly + vbInformation, "Error"
   Me.SKU_Number.SetFocus
   Exit Sub
End If
'Set up variables
SKU = Me.SKU_Number.Value
LinkCriteria = "[SKU_Number] = '" & SKU & "'"
'make sure record does not already exist
If DCount("SKU_Number", "tbl_FG_SKUs", LinkCriteria) > 0 Then
   Response = MsgBox("This component already exists.",  vbOKOnly + vbInformation, "Record Already Exists")
   Else
   Response = MsgBox("You are about to add a Component to the list. Please review details in the box below." & vbCrLf & vbCrLf & "Click YES to add record. Click NO to  revise.", vbYesNo, "Warning")
End If
If Response = vbYes Then
   DoCmd.GoToRecord , , acNewRec
End If
End Sub
Go to the top of the page
 
+
datAdrenaline
post Jun 2 2011, 10:20 PM
Post #3

UtterAccess Editor
Posts: 16,740
From: Northern Virginia, USA



With out seeing your application, I have a few concerns with the code. I am assuming that you want to ensure certain fields are not null (or a Zero Length String - ZLS) and your form is bound to a table. However, your code will only trap for that if the user clicks the Add button. However, a user can easily be on a new record, then navigate to another record or press the F9 key and "unclean" data will be saved in the table with no warning at all. So if you wish to stay with a bound form, which is a great way to input data, there are a host of ways to validate your data and allow (or prevent) the use of the F9 key and record navigation to save new/edited records. If this commentary has piqued your interest, please let use know and we can procede to assist in creating a form that provides clean data entry that accounts for the multiple ways Access saves a record in a table.
Go to the top of the page
 
+
Skimmer
post Jun 3 2011, 09:50 AM
Post #4

UtterAccess Member
Posts: 39



I was looking for a way to exit the sub in the middle there, but didn't know you could put "exit sub" within the If Statement (though it seems so obvious now!). At any rate, it's an incredibly helpful thing to know!! Thanks so much, this code works perfectly.
Have a good day!
Kim
Go to the top of the page
 
+
Skimmer
post Jun 3 2011, 10:00 AM
Post #5

UtterAccess Member
Posts: 39



Hi there,
would be interested in learning anything anyone wanted to teach me about securing my data! Thanks for the offer.
Out of curiosity, the fields in my form here are set in my tables to not accept duplicates, so Access itself will (theoretically) block the user from adding existing info via the form. I just thought it would flow nicer to have it in code with message boxes people can understand instead of Access Speak jumping out and scaring them to death. Am I correct in assuming the data is protected under Access duplication rules? Or is another layer (blocking F9 for example) still important?
Thanks again,
Kim
Go to the top of the page
 
+
Skimmer
post Jun 3 2011, 10:12 AM
Post #6

UtterAccess Member
Posts: 39



Shoot....just realized the code actually still isn't working. I still get the 'can't go to record' error (2105) referring to the
ocmd.gotorecord,,acnewrec
line in the code.
Is it possible there's something in the form properties that is causing this error?
This code works perfectly for two other forms that are more or less identical (updating different tables, but similar structure). But for some reason this form is being stubborn...
any thoughts?
Thanks!
Kim
Go to the top of the page
 
+
datAdrenaline
post Jun 3 2011, 11:05 AM
Post #7

UtterAccess Editor
Posts: 16,740
From: Northern Virginia, USA



Any chance of posting your database (or a the problematic portion of it) with obfuscated data?
Go to the top of the page
 
+
datAdrenaline
post Jun 3 2011, 11:09 AM
Post #8

UtterAccess Editor
Posts: 16,740
From: Northern Virginia, USA



Oh yes ... the RT 2105 error ... you should save before you navigate to a new record because you are already on a new record.
If Me.Dirty Then Me.Dirty = False
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Go to the top of the page
 
+
Skimmer
post Jun 3 2011, 12:09 PM
Post #9

UtterAccess Member
Posts: 39



The real problem was that I was missing a field on the form that is a required field in the table. Therefore, no new record could be created (since that field would be left blank). It's all fixed now.
Thanks again for you help!
-Kim
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 23rd September 2014 - 09:25 PM