Write to a specific Record in a table

[Guest]

I need to record the next output file index number to a table. This table is
called "tlkpStoredStrings". It has three columns: "VariableName",
StoredText", "Description". I want to write this index number to the record
whose name is "File Number". Every way I try, I get an error: "Type
Mismatch". Here is what I wrote:


Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
If strFileNumber = "99" Then strFileNumber = "00"
strFileNumber = strFileNumber + 1
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")

[Here I need a way to move to the record in which "VariableName" =
"FileNumber"

With rst
.Edit
!StoredText = Format$(strFileNumber, "00")
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

I would appreciate help with this...

 

[John Nurick]

Hi Richard,

strFileNumber = strFileNumber + 1

You're trying to add a number to a string. Try something like

strFileNumber = Format(CLng(strFileNumber) + 1, "00")

Your code would also skip from "99" to "01". If that's not what you
want, dump the
If strFileNumber ...
and try something like
strFileNumber = Format((CLng(strFileNumber) + 1) Mod 100, "00")

I need to record the next output file index number to a table. This table is
called "tlkpStoredStrings". It has three columns: "VariableName",
StoredText", "Description". I want to write this index number to the record
whose name is "File Number". Every way I try, I get an error: "Type
Mismatch". Here is what I wrote:


Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
If strFileNumber = "99" Then strFileNumber = "00"
strFileNumber = strFileNumber + 1
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")

[Here I need a way to move to the record in which "VariableName" =
"FileNumber"

With rst
.Edit
!StoredText = Format$(strFileNumber, "00")
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

I would appreciate help with this...

 

[Guest]

Hello again John,

I had wanted to skip strFileNumber = "00", but like your method. My "Type
Mismatch" error turned out due to a reference to an old version of Microsoft
DAO Object library. So that's fixed now.

Wondering if you have any further comment on my procedure now. Is my
If...Then...EndIf section the best way to move to the correct record to
insert my updated strFileNumber.

Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
strFileNumber = Format((CLng(strFileNumber) + 1) Mod 100, "00")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")
With rst
If !VariableName <> "FileNumber" Then
Do Until !VariableName = "FileNumber"
.MoveNext
Loop
End If
.Edit
!Lookup = strFileNumber
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

Thanks...Richard

Hi Richard,

strFileNumber = strFileNumber + 1

You're trying to add a number to a string. Try something like

strFileNumber = Format(CLng(strFileNumber) + 1, "00")

Your code would also skip from "99" to "01". If that's not what you
want, dump the
If strFileNumber ...
and try something like
strFileNumber = Format((CLng(strFileNumber) + 1) Mod 100, "00")

I need to record the next output file index number to a table. This table is
called "tlkpStoredStrings". It has three columns: "VariableName",
StoredText", "Description". I want to write this index number to the record
whose name is "File Number". Every way I try, I get an error: "Type
Mismatch". Here is what I wrote:


Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
If strFileNumber = "99" Then strFileNumber = "00"
strFileNumber = strFileNumber + 1
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")

[Here I need a way to move to the record in which "VariableName" =
"FileNumber"

With rst
.Edit
!StoredText = Format$(strFileNumber, "00")
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

I would appreciate help with this...

 

[Kevin K. Sullivan]

If you want to write to a specific record, only open that record: open a
recordset on a query with a WHERE clause:

Dim strSQL as String
strSQL = "SELECT * FROM tlkpStoredStrings"
strSQL = strSQL & " WHERE VariableName='FileNumber'"
Set rst = dbs.OpenRecordset(strSQL)
With rst
.Edit
!Lookup = strFileNumber
.Update
.Close
End With
-------
Better yet, you can make the whole change in an Update query:


Dim strSQL as String

strSQL = "UPDATE tlkpStoredStrings"
strSQL = strSQL & " SET Lookup='" & FileNumber & "'"
strSQL = strSQL & " WHERE VariableName='FileNumber'"

dbs.Execute strSQL


HTH,

Kevin

Hello again John,

I had wanted to skip strFileNumber = "00", but like your method. My "Type
Mismatch" error turned out due to a reference to an old version of Microsoft
DAO Object library. So that's fixed now.

Wondering if you have any further comment on my procedure now. Is my
If...Then...EndIf section the best way to move to the correct record to
insert my updated strFileNumber.

Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
strFileNumber = Format((CLng(strFileNumber) + 1) Mod 100, "00")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")
With rst
If !VariableName <> "FileNumber" Then
Do Until !VariableName = "FileNumber"
.MoveNext
Loop
End If
.Edit
!Lookup = strFileNumber
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

Thanks...Richard

:

Hi Richard,

strFileNumber = strFileNumber + 1

You're trying to add a number to a string. Try something like

strFileNumber = Format(CLng(strFileNumber) + 1, "00")

Your code would also skip from "99" to "01". If that's not what you
want, dump the
If strFileNumber ...
and try something like
strFileNumber = Format((CLng(strFileNumber) + 1) Mod 100, "00")

I need to record the next output file index number to a table. This table is
called "tlkpStoredStrings". It has three columns: "VariableName",
StoredText", "Description". I want to write this index number to the record
whose name is "File Number". Every way I try, I get an error: "Type
Mismatch". Here is what I wrote:


Public Sub SaveNextFileNumber(strFileNumber As String)
Dim dbs As Database
Dim rst As Recordset
If strFileNumber = "99" Then strFileNumber = "00"
strFileNumber = strFileNumber + 1
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tlkpStoredStrings")

[Here I need a way to move to the record in which "VariableName" =
"FileNumber"

With rst
.Edit
!StoredText = Format$(strFileNumber, "00")
.Update
.Close
End With
Set dbs = Nothing: Set rst = Nothing
End Sub

I would appreciate help with this...