Solved: Unbound Form, VBA, SQL

[ScriptHam]

Hi :hi:

I am experiencing problems with an unbound form. The form, frmTestQuery, deals with table tblBatchCardGeneral in CompositionWT.mdb

I have a large maintenance job due and wish to do it programmatically rather than amend 120+ records manually.
Initially I will define a SELECT query until I am happy with the result and then move to Action Query strings.
Have obtained this error though not at present, 2147352567 error for the NextRecord command button.

Running Access 2003 on Windows XP installed on a pc networked over a LAN powered by ms NTserver 2003 using SBS.


Option Compare Database
Option Explicit

'Dim remoteConnection As New ADODB.Connection
'Dim rsProducts As New ADODB.Recordset

Private Sub Form_Load()

Dim db As Database 'was As DAO.Database
Dim rsProducts As Recordset 'was As DAO.Recordset
'Dim qdf As QueryDef

Set db = CurrentDb
'Set qdf = "SELECT * FROM tblBatchCardGeneral"
Set rsProducts = db.OpenRecordset("tblBatchCardGeneral")
'Set rs = qdfParmQry.OpenRecordset(qdf)

If (rsProducts.BOF And rsProducts.EOF) Then
MsgBox "Recordset is empty"
End If

Me.txtCount = rsProducts.RecordCount

End Sub

Private Sub cmdStart0_Click() ' Other Command Buttons for navigation are similar
On Error GoTo Err_cmdStart0_Click

Dim db As Database
Dim rsProducts As Recordset
Dim qryStr As String

Set db = CurrentDb
'Set qryStr = "SELECT * FROM tblBatchCardGeneral"
Set rsProducts = db.OpenRecordset("tblBatchCardGeneral")

rsProducts.MoveFirst
Me.txtBCGProductName = rsProducts!ProductName
Rem DoCmd.GoToRecord , , acFirst

Exit_cmdStart0_Click:
Exit Sub

Err_cmdStart0_Click:
MsgBox "There was an error retrieving data from the database" & Err.Number & ", " & Err.Description
Resume Exit_cmdStart0_Click

End Sub




This gives a record count of 126 which is correct but only navigates through 2 records. There are buttons on the form for firstl, last, pervious, next, all with very similar code.

Can anyone see why I can't navigate through this recordset?

I don't know whether I need to Connect to the database, I should have thought not as there is evidence of a connection (counting the records in the table) with the code as it is and also I initialise using
Set db = Currentdb
Any ideas?

If I use Set rsProducts = db.OpenRecordset("tblBatchCardGeneral") I get more progress than
Set rsProducts = db.OpenRecordset("SELECT * FROM tblBatchCardGeneral")
And more also than
Set strSQL = "SELECT * FROM tblBatchCardGeneral"
Set rsProducts = db.OpenRecordset(strSQL)

I am used to finishing an SQL string with ; but this seems to be unnecessary, right or wrong in your opinion?

Can anyone shed any light on why this is happening?

:help
I look forward to hearing from you.

ScriptHam

[xCav8r]

:hi:

Looks like you're doing DAO, so you need to type these objects specifically as DAO objects.

DoCmd.GoToRecord only works with forms that are bound to a recordset.

You need Set db = CurrentDb, because you have to set all object variables to use them, and you need a DAO.Database object to open a recordset.

MS Access doesn't care if your SQL statements end with ; or not. If you work with other databases, not using it can be a sloppy habit that will cause problems. :)

[ScriptHam]

Hi xcav8tr :hi:
Thankyou for your reply.

DAO: Amended throughout to:
Dim db As DAO.Database 'was As Database


DoCmd.GoToRecord not used. Perhaps it should be used though?
Instead used:

Dim db As DAO.Database 'was As Database
Dim rsProducts As DAO.Recordset
'Dim strQry As String

Set db = CurrentDb
'Set strQry = "SELECT * FROM tblBatchCardGeneral"
Set rsProducts = db.OpenRecordset(tblBatchCardGeneral)


Now amended to

Dim db As DAO.Database 'was As Database
Dim rsProducts As DAO.Recordset
Dim strQry As String

Set db = CurrentDb
Set strQry = "SELECT * FROM tblBatchCardGeneral"
Set rsProducts = db.OpenRecordset(strQry)

Giving: Error = Compile error object required

Set db=CurrentDb: Used throughout. All should be OK here I think.

SQL; all should be OK here then.

The problems are the same except for the new error message.
Are we homing in closer do you think? : pray2:

ScriptHam

[xCav8r]

You're prolly getting the error from

Set strQry = "SELECT * FROM tblBatchCardGeneral"

This isn't an object variable, so don't use Set. Change to....

strQry = "SELECT * FROM tblBatchCardGeneral"

[ScriptHam]

Hi xcav8tr
:hi:
Made this change but it still does not run.

Will advise if I can find anything during today.

Many thanks
ScriptHam

[xCav8r]

What line of the code is generating an error and what is the error number? And why is this form unbound?

[ScriptHam]

Hi All

Had forgotten that this was still outstanding. :think:

Took Holiday a few days after the previous post and forgot about it.
Tackled the job mostly by manual means.
Probably had bitteen off more than I could chew - still a nembie.

Many thanks to all contributors. Will mark this thread as finished.

ScriptHam