dbSeeChanges

[ZetecS]

Good morning all,

I have a form which acts as a search form to find records within the database.

The database was created in access but has since had the table upsized to SQL 2005. The Search Form was working but now I get the following error displayed.

You must see the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

The debugger takes me the the following lines of code.

Set db = CurrentDb
Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

Can anyone help me with where I should be putting the dbSeeChanges.

Many thanks,

 

[vbaInet]

Can we see the entire code. Please enclose it in Code tags -->

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[ZetecS]

Private Sub Search_Click()
Dim db As Database, rcd As DAO.Recordset, lngCount As Long, intRtn As Integer
Dim strWhere As String
strWhere = ""
 
If Not IsNull(Me!txtLog) Then
strWhere = "[LogNumber] Like " & Chr(34) & Me!txtLog
If Right$(Me!txtLog, 1) = "*" Then
strWhere = strWhere & Chr$(34)
Else
strWhere = strWhere & "*" & Chr(34)
End If
End If
 
If Not IsNull(Me!txtEmployee) Then
If strWhere = "" Then
strWhere = "[Employee] Like " & Chr(34) & Me!txtEmployee
Else
strWhere = strWhere & "AND [Employee] Like " & Chr$(34) & Me!txtEmployee
End If
If Right$(Me!txtEmployee, 1) = "*" Then
strWhere = strWhere & Chr$(34)
Else
strWhere = strWhere & "*" & Chr$(34)
End If
End If
 
If Not IsNull(Me!txtArea) Then
If strWhere = "" Then
strWhere = "[Area] Like " & Chr(34) & Me!txtArea
Else
strWhere = strWhere & "AND [Area] Like " & Chr$(34) & Me!txtArea
End If
If Right$(Me!txtArea, 1) = "*" Then
strWhere = strWhere & Chr$(34)
Else
strWhere = strWhere & "*" & Chr$(34)
End If
End If
 
If strWhere = "" Then
MsgBox "No Criteria specified.", vbExclamation, "Bad Search"
Exit Sub
'Else
'MsgBox "strWhere = " & strWhere, vbExclamation
'Exit Sub
End If
 
' Turn on Hourglass
DoCmd.Hourglass True
If CurrentProject.AllForms("SupportForm").IsLoaded = False Then
Set db = CurrentDb
[B]Set rcd = db.OpenRecordset("select " & _[/B]
[B]"IT_SupportTable.LogNumber " & _[/B]
[B]"FROM IT_SupportTable" & _[/B]
[B]" WHERE " & strWhere & ";")[/B]
' If none found, then tell them and make me visible to try again
If rcd.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Job numbers meet your criteria", vbInformation, "Installs Database"
strWhere = ""
rcd.Close
Exit Sub
End If
gstrWhereJob = strWhere
Me.Visible = False
rcd.MoveLast
lngCount = rcd.RecordCount
DoCmd.Hourglass False
DoCmd.OpenForm FormName:="SupportForm", whereCondition:=strWhere
End If
End Sub

I didn't write the original my manager did. I have just gone thru and edit bits for it too work with my database.

 

[vbaInet]

What line exactly does it highlight? And what is the error code?

 

[Kiwiman]

Howzit

A while since I connected to a SQL Server but is it not something like

set rs = Openrecordset(yourSQL,dbSeeChanges)

 

[ZetecS]

What line exactly does it highlight? And what is the error code?

If you look in my OP I have said what the error code is and what is highlighted when the debugger runs. My problem is I don't know what to change :/.

Cheers,

 

[vbaInet]

That would go in the second parameter. But my understanding is dbSeeChanges and dbInconsistent go hand-in-hand.

 

[ZetecS]

Howzit

A while since I connected to a SQL Server but is it not something like

set rs = Openrecordset(yourSQL,dbSeeChanges)

What would I declare rs as and also how would I fit that line of code into my exisiting code?

Cheers,

 

[vbaInet]

If you look in my OP I have said what the error code is and what is highlighted when the debugger runs. My problem is I don't know what to change :/.

Cheers,

You presented two lines of code. The debugger highlights one line. Which one is it?

 

[ZetecS]

You presented two lines of code. The debugger highlights one line. Which one is it?

I will hopefully try to brief you as too what exactly happens and I hope it can help you help me .

When I enter a LogNumber into the text box on the form then hit the Search button I have a window pop up saying:

You must see the dbSeeChanges... blah, blah, blah.

I then proceed to the debugger and its take me too the following lines

Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

which are all highlighted yellow. I can only assume the reason for this is because the open bracket starts on the first line then shuts on the 4th line?

Cheers,

 

[G37Sam]

The SQL table you're linked to has an identity column, all you have to do is change this

Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

to

Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";",dbOpenDynaset, dbSeeChanges)

 

[Kiwiman]

Howzit

You already have the SQL specified

See below

Set db = CurrentDb
[B]Set rcd = db.OpenRecordset("select " & _[/B]
[B]"IT_SupportTable.LogNumber " & _[/B]
[B]"FROM IT_SupportTable" & _[/B]
[B]" WHERE " & strWhere & ";",[COLOR="Red"][B]dbSeeChanges[/B][/COLOR])[/B]
[/QUOTE]

I normally set the SQL statement to a variable then use the variable in the OpenRecordset

[CODE]
Dim strSQL as string

stSQL = "Your SQL Statement"

set rcd = db.openrecordset(strSQL,dbSeeChanges)

 

[ZetecS]

The SQL table you're linked to has an identity column, all you have to do is change this

Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

to

Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";",dbOpenDynaset, dbSeeChanges)

Spot on mate, spot on thank you for that great help.

 

[ZetecS]

Howzit

You already have the SQL specified

See below



I normally set the SQL statement to a variable then use the variable in the OpenRecordset

Dim strSQL as string
 
stSQL = "Your SQL Statement"
 
set rcd = db.openrecordset(strSQL,dbSeeChanges)

Thanks for your reply and help man, yourself and G37Sam and sorted it for me. Thanks a lot much appreciated.

 

[G37Sam]

Glad I could be of help

Cheers,
Sam