Having Problem w/ Code

[Joel27]

I used this code:


Dim SQL1 As String, Rs As Recordset, Db As Database, Ctl As Control
Set Db = CurrentDb()
If IsNull(Me.YourFieldName) Or Me.YourFieldName = "" Then
MsgBox CurrentUser() & ", please add an appropriate drawing number.", vbInformation, "Drawing number required..."
Set Ctl = Me.YourFieldName
Response = acDataErrContinue
Ctl.Undo
Ctl.SetFocus
Else
SQL1 = "SELECT YourTableName.* FROM YourTableName" 'check for dup names
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
GoTo NewDrawing
End If
Rs.MoveFirst
Do Until Rs.EOF
If Rs!YourDrawingField = Me.YourFieldName Then
Rs.Close
MsgBox "There is already a drawing with number ''" & Me.YourFieldName & "''. Please chose an alternate.", vbInformation, "Drawing Number Exists!"
Me.YourFieldName = Empty
Exit Sub
End If
Rs.MoveNext
Loop
Rs.Close

NewDrawing:
SQL1 = "SELECT YourTableName.* FROM YourTableName"
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset, dbAppendOnly)
Rs.addnew
Rs!YourDrawingField = Me.YourFieldName
Rs.Update
Rs.Close
End If

I'm Having a heck of a time with what table names are to go where I'm preventing duplicate in the Electrical table
What am I to put in the : YourDrawingField ?

 

[ghubbell]

Hi Joel, kind of missed you over here...sorry...

YourDrawingField is the name of the field in your table: should be realistically the same name as YourFieldName.

Please don't forget: if either is named like:

Drawing Number you must type them in like Drawing_Number or Access will get all confused. If it is one "word" DrawingNumber , you have no issues. Keep at it. I'm watching now...! Gord
ghubbell@total.net

 

[Joel27]

Get an error on (me.drawlog)


If IsNull(Me.YourFieldName) Or Me.YourFieldName = "" Then

Member or data method not found!


what do you thinks the error here

J

 

[Joel27]

Scratch that last one

i get an error on 'response'
Response = acDataErrContinue

 

[ghubbell]

Joel, paste your code as you've built it in here... Let's have a look... Gord
ghubbell@total.net

 

[Joel27]

Private Sub Drawing_Number_AfterUpdate()

Dim SQL1 As String, Rs As Recordset, Db As Database, Ctl As Control
Set Db = CurrentDb()
If IsNull(Me.Drawing_Number) Or Me.Drawing_Number = "" Then
MsgBox CurrentUser() & ", please add an appropriate drawing number.", vbInformation, "Drawing number required..."
Set Ctl = Me.Drawing_Number
Response = acDataErrContinue
Ctl.Undo
Ctl.SetFocus
Else
SQL1 = "SELECT drawlog.* FROM drawlog" 'check for dup names
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
GoTo NewDrawing
End If
Rs.MoveFirst
Do Until Rs.EOF
If Rs!Drawing_Number = Me.Drawing_Number Then
Rs.Close
MsgBox "There is already a drawing with number ''" & Me.Drawing_Number & "''. Please chose an alternate.", vbInformation, "Drawing Number Exists!"
Me.Drawing_Number = Empty
Exit Sub
End If
Rs.MoveNext
Loop
Rs.Close

NewDrawing:
SQL1 = "SELECT drawlog.* FROM drawlog"
Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset, dbAppendOnly)
Rs.AddNew
Rs!Drawing_Number = Me.Drawing_Number
Rs.Update
Rs.Close
End If

Here we go.
J

 

[ghubbell]

Ooops! we forgot to "Declare a variable" my fault I think so sorry. Bad Gord! Bad!


Dim SQL1 As String, Rs As Recordset, Db As Database, Ctl As Control,Response as integer

add that last bit and see how it goes...... Gord
ghubbell@total.net

 

[Joel27]

I tried the code up above and no such luck yet

any thoughts

J

 

[ghubbell]

Morning Joel, do me a favor if you'd like: Copy, Compact~(zip if you have it) and email me your Db or your problem area. I'll take a closer look at it here. My treat. I'll post my findings here too. X-) Gord
ghubbell@total.net

 

[Joel27]

I just zipped it and sent it you shoul dget it in about 10 minutes

J