updating a record

[markjkubicki]

the first message box ("Option (criteria) selected is ") is displayed;
but, nothing afterwards (including the second: "Number of records = ")

I checked the spelliong of tblFixtureSchedulePrintOptions, PresetOption, and frmPresetOption - all are correct, and frmPresetOption is a number field

[ssanfu]

the first message box ("Option (criteria) selected is ") is displayed;
but, nothing afterwards (including the second: "Number of records = ")

I checked the spelliong of tblFixtureSchedulePrintOptions, PresetOption, and frmPresetOption - all are correct, and frmPresetOption is a number field

OK, Try this:

Dim sSQL As String
'==============================
'for testing
'==============================
Dim R as DAO.Recordset
Dim RC as Long 'record count

MsgBox "Option (criteria) selected is " & Me.frmPresetOption

sSQL = " SELECT *" _
sSQL = sSQL & " FROM tblFixtureSchedulePrintOptions"
sSQL = sSQL & " WHERE [PresetOption] = " & Me.frmPresetOption & ";"

MsgBox "sSQL = " & sssql

' open recordset

'my spelling error
Set R = Currentdb.OpenRecordset(sSQL)
RC = R.Recordcount
R.Close
Set R = Nothing
MsgBox "Number of records = " & RC
'==============================
'end testing
'==============================


'clear table
sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
CurrentDB.Execute sSQL, dbfailonerror
'append new record
sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"

MsgBox "Insert string = " & ssql

CurrentDB.Execute sSQL, dbfailonerror
'requery current form
Me.Requery

Do the sSQL strings look right??

[markjkubicki]

so, I tried the code, and got this information
- the option number choosen - check
- (1) matching record in the look-up table for that option - check
- sSQL for the deletion syntax - check
- sSQL fro the insert syntax -check
- all good

and then I changed the code (see below)
and couldn't get past a stumbling block (noted in the code as: "this is where I got stuck.."


here's the code i used:

Private Sub frmPresetOption_beforeUpdate(Cancel As Integer)
Dim sSQL As String
'==============================
'for testing
'==============================
Dim R As DAO.Recordset
Dim rstTBE As DAO.Recordset
Dim RC As Long 'record count
MsgBox "Option (criteria) selected is " & Me.frmPresetOption

' test for matching record
sSQL = " SELECT *"
sSQL = sSQL & " FROM tblFixtureSchedulePrintOptions"
sSQL = sSQL & " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
MsgBox "sSQL = " & sSQL
Set R = CurrentDb.OpenRecordset(sSQL)
RC = R.RecordCount
R.Close
Set R = Nothing
MsgBox "Number of matching records records in TBL = " & RC

'==============================
'this next line is giving me trouble
sSQL_TBE = "select * from [tbeFixtureSchedulePrintOptions];" 'tbeFixtureSchedulePrintOptions is spelled correctly
Set rstTBE = sSQL_TBE
RC = rstTBE.RecordCount
MsgBox "RecordCount in TBE = " & RC
'==============================

'clear table
sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
MsgBox "Delete string = " & sSQL

CurrentDb.Execute sSQL, dbFailOnError

RC = rstTBE.RecordCount
MsgBox "RecordCount in TBE after delete = " & RC

'append new record
sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
MsgBox "Insert string = " & sSQL
CurrentDb.Execute sSQL, dbFailOnError
RC = rstTBE.RecordCount
MsgBox "RecordCount in TBE after delete = " & RC
rstTBE.Close
Set rstTBE = Nothing
'requery current form
Me.Requery
'==============================

[ssanfu]

Don't know if you solved this......

'==============================
'this next line is giving me trouble
sSQL_TBE = "select * from [tbeFixtureSchedulePrintOptions];" 'tbeFixtureSchedulePrintOptions is spelled correctly
Set rstTBE = sSQL_TBE
RC = rstTBE.RecordCount
MsgBox "RecordCount in TBE = " & RC
'==============================

You can't just set the SQL equal to variable. Use:

Set rstTBE = Currentdb.OpenRecordset(sSQL_TBE)
If not rstTBE.BOF and not rstTBE.EOF then
   rstTBE.MoveLast
   RC = rstTBE.RecordCount
   MsgBox "RecordCount in TBE = " & RC
   rstTBE.MoveFirst
End If