If Record Doesnt exist, then add... else Exit Sub 1

[JPeters]

Okay Brains, here's one that I am sick of searching for.
I dreamt this up, but it won't work - obviously, maybe one of you can tweak it for me. This code below tells me that there is no Current Record ... well duh! I want it to add one if the record doesn't exist, and if it already does - I want it to treat it like a normal form open. Maybe I'm going about it all wrong, but I'm hoping that this code is somehere on the path to the COrrect Code.. FYI: 'assignmentnumber' is the txtbox in my form..

Private Sub Form_Open(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblpropertysurvey&quot

With RS
RS.Move (Me.CurrentRecord) 'My problem is around here
If RS[assignmentnumber] = assignmentnumber Then
Exit Sub
Else
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
End If
End With

Set DB = Nothing
Set RS = Nothing
End Sub

My prob is that if the record doens't exist then there isn't a record to move to - so I get an error. ANy clues?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[GHolden]

How about something like search record set for assignmentnumber if not found add it, like this...

with rs
.FindFirst "assignmentnumber = '" & assignmentnumber & "'"
If .NoMatch Then
'add new record
Else
'do nothing End If
end with

 

[GHolden]

How about something like search record set for assignmentnumber if not found add it, like this...

sorry if assignmentnumber is actually a number then no single quotes in findfirst

with rs
.FindFirst "assignmentnumber = " & assignmentnumber If .NoMatch Then
'add new record
Else
'do nothing End If
end with

 

[JPeters]

Brilliant! I need to read up on my recordset commands! I'll try this and get back to you. Thank you!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

Still getting errors since i'm not good a the Findfirst method... Operation not supported... and then it highlights the Findfirst line...

Private Sub Form_Open(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblpropertysurvey&quot


With RS
'If RS![assignmentnumber] = assignmentnumber Then
.FindFirst assignmentnumber 'This is my text box value
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With


Set DB = Nothing
Set RS = Nothing
End Sub

It says Operation not supported.. ACK .. (shoots monitor)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[FMAQ]

With RS
'If RS![assignmentnumber] = assignmentnumber Then
<<THAT BELOW IS WRONG IT MUST BE LIKE THIS>>
<< .FINDFIRST &quot;ASSIGNMENTNUMBER = '&quot; &&quot; FORM![YOUR FORM NAME].ASSIGNMENTNUMBER'&quot;
** WRONG ==>> .FindFirst assignmentnumber 'This is my text box value
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With

So try correcting like this an tell us after doing it, Ok

By

Felix Arismendi
Peru

 

[FMAQ]

With RS
'If RS![assignmentnumber] = assignmentnumber Then
<<THAT BELOW IS WRONG IT MUST BE LIKE THIS>>
<< .FINDFIRST &quot;ASSIGNMENTNUMBER = '&quot; &&quot; FORM![YOUR FORM NAME]!ASSIGNMENTNUMBER'&quot;
** WRONG ==>> .FindFirst assignmentnumber 'This is my text box value
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With

So try correcting like this an tell us after doing it, Ok

By

Felix Arismendi
Peru

 

[FMAQ]

Excuse me theres an error,
With RS
'If RS![assignmentnumber] = assignmentnumber
Then
<<THAT BELOW IS WRONG IT MUST BE LIKE THIS>>
<< .FINDFIRST &quot;ASSIGNMENTNUMBER = '&quot; & FORM![YOUR FORM NAME]!ASSIGNMENTNUMBER & &quot;'&quot;
** WRONG ==>> .FindFirst assignmentnumber 'This is my text box value
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With

So try correcting like this an tell us after doing it, Ok

By

Felix Arismendi
Peru




Inappropriate post?

 

[FMAQ]

Excuse me theres an error,
With RS
'If RS![assignmentnumber] = assignmentnumber
Then
<<THAT BELOW IS WRONG IT MUST BE LIKE THIS>>
<< .FINDFIRST &quot;ASSIGNMENTNUMBER = '&quot; & FORM![YOUR FORM NAME]!ASSIGNMENTNUMBER & &quot;'&quot;
** WRONG ==>> .FindFirst assignmentnumber 'This is my text box value
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With

So try correcting like this an tell us after doing it, Ok

By

Felix Arismendi
Peru

 

[cmmrfrds]

Still getting errors since i'm not good a the Findfirst method... Operation not supported... and then it highlights the Findfirst line...

If you are using access 2000 then Findfirst is not a valid object unless you change to a DAO recordset. Access 2000 recordset objects default to ADO. ADO has a Find or Filter method, and Filter is the preferred way of finding a record.
ie. RS.Filter = assignmentnumber

If you are going to use Access 2000 then read up on the ADO Objects.

You can use the Findfirst if you change to DAO, Like

Dim DB As DAO.Database
Dim RS As DAO.Recordset

then the code that was provided should work.

 

[GHolden]

OK.

Not used 2000, assuming it's the same as VB6 then try

rs.movefirst
rs.find &quot;assignmentnumber = '&quot; & form!formname!assignmentnumber & &quot;'&quot;

if rs.eof then
'addnew
end if

 

[JPeters]

Thank you all for the replies over the weekend. I'll work on this today and let you know what happened.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

Okay, after some playing - I've come up with this.
And I'm still recieving the &quot;Operation Not Supported&quot; error. I am using AC97, by the way. What a head-ache! ..


Private Sub Form_Open(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(&quot;tblpropertysurvey&quot

With RS
.MoveFirst
RS.FindFirst &quot;assignmentnumber = '&quot; & Forms![frmchurchproperty]![assignmentnumber] & &quot;'&quot;
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With
End Sub

Code seems to be syntax sound, but it gives me that Operation NOt supported error. Any ideas on what I should change?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[GHolden]

Got it !!!!

Set RS = DB.OpenRecordset(&quot;tblpropertysurvey&quot

needs

Set RS = DB.OpenRecordset(&quot;tblpropertysurvey&quot;,dbOpenDynaset)

I hope

 

[JPeters]

Okay .. about to go nutty with confusion and frustration. In logic this code should work.. Yet I keep getting errors.

I've tried 2 codes now. Here's the first and the error it gives me.

*******************************************
FIRST CODE:
Private Sub Form_Open(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(&quot;tblpropertysurvey&quot;, dbOpenDynaset)

With RS
'If RS![assignmentnumber] = assignmentnumber Then
'RS.FindFirst &quot;assignmentnumber = '&quot; & Forms![frmchurchproperty]![assignmentnumber] & &quot;'&quot;
.FindFirst RS!assignmentnumber = assignmentnumber
'If RS!assignmentnumber = assignmentnumber Then
If RS.NoMatch Then
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
Else
Exit Sub
End If
End With
End Sub

ERROR: It gives a data type mismatch in critera expression error and the debug goes to this line

RS.FindFirst &quot;assignmentnumber = '&quot; & Forms![frmchurchproperty]![assignmentnumber] & &quot;'&quot;

*******************************************************
SECOND CODE: I got witty and decided, well maybe this will work instead - in logic this works.. I dont know why it won't.

Private Sub Form_Open(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(&quot;tblpropertysurvey&quot

With RS
Do While Not RS.EOF
If RS!assignmentnumber = Me.assignmentnumber Then
Exit Do
Exit Sub
Else
RS.MoveNext
End If
Loop
.AddNew
RS![assignmentnumber] = assignmentnumber
.Update
End With
End Sub

It will only add a new record if it NEVER finds the RS!Assign... and the Me.assign... equal to each other because it exits both the loop and the sub upon finding a match.

ERROR: It never adds the new record to my recordset. The form will open fine, but even if it doens't find a match (which it obviously doesn't if I have only 1 record in the table and it's a fake record) it still won't perform the .AddNew function. So I'm left with no data being entered into the table.
*******************************************
What do you all think? We're gonna get this! ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[GHolden]

Right this definately works I promise
I have textbox txtNumber
and command button Command0
If I type text into txtNumber and then click on
command0 it adds txtNumber to the table if it doesn't
already exist.


Private Sub Command0_Click()
Dim DB As Database
Dim RS As Recordset
Dim strID As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(&quot;tblEmployee&quot;, dbOpenDynaset)

With RS
.FindFirst &quot;EmployeeID = '&quot; & txtNumber & &quot;'&quot;
If .NoMatch Then
.AddNew
!EmployeeID = txtNumber
.Update
Else
'Do nothing at all
End If
End With

Set DB = Nothing
Set RS = Nothing

End Sub

 

[JPeters]

Gholden,
Thanks for hanging in there with me. The above still didn't work. But I decided that it was about time I did some heavy research in the FindFirst and Seek functions. I found that I had my syntax wrong for the .FindFirst.

Instead of: .FindFirst &quot;assignmentnumber = '&quot; & assignmentnumber & &quot;'&quot;

It works like this: .FindFirst &quot;assignmentnumber = &quot; & assignmentnumber

Thanks for all your time and for hanging in there with me!
Have a star!

-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[GHolden]

Ah...

Yes, you only need the single quotes if the variable you are looking for is a string.

If it's numeric you have to leave them out.

Best of luck !!