Help: Using a button do add data in existing record

[alexius]

To give you some background, what I'm trying to do is create an interface that lets people create game scenarios. Through various screens (forms) they can select different options. These are then saved in a final tblScenario.

I've been able to let them fill out txt boxes (Scenario Name, etc.) and choose options from a cmb list box (Xbox, PS3, etc.). However, each time they click the button, the cell gets put on a new row.

How do I change the code to insert all of these into the same (or active) record?

For the first one (txtScenarioName) I want it to add a new record. But thereafter, each one should be added to that same record. Here's an example of the ones that come after the Name screen:

Private Sub btnPlatformNext_Click()

Dim intPlatform As Integer

cmbPlatform.SetFocus
intPlatform = cmbPlatform.Value

Call CreatePlatform(intPlatform)

End Sub

Private Sub CreatePlatform(plat As Integer)

Dim strSQL As String

strSQL = "Insert into tblScenario (scenarioPlatform) values ( """ & plat & """)"

CurrentDb.Execute (strSQL)

End Sub

Thanks in advance for any help!

 

[BobMcClellan]

To give you some background, what I'm trying to do is create an interface that lets people create game scenarios. Through various screens (forms) they can select different options. These are then saved in a final tblScenario.

I've been able to let them fill out txt boxes (Scenario Name, etc.) and choose options from a cmb list box (Xbox, PS3, etc.). However, each time they click the button, the cell gets put on a new row.

How do I change the code to insert all of these into the same (or active) record?

For the first one (txtScenarioName) I want it to add a new record. But thereafter, each one should be added to that same record. Here's an example of the ones that come after the Name screen:

Private Sub btnPlatformNext_Click()

Dim intPlatform As Integer

cmbPlatform.SetFocus
intPlatform = cmbPlatform.Value

Call CreatePlatform(intPlatform)

End Sub

Private Sub CreatePlatform(plat As Integer)

Dim strSQL As String

strSQL = "Insert into tblScenario (scenarioPlatform) values ( """ & plat & """)"

CurrentDb.Execute (strSQL)

End Sub

Thanks in advance for any help!

If you look at your strSQL string it says INSERT. That's why you are getting a new row. What you need to do is identify the 1st choice as the initial insert. THEN, get the ID for that row and use it in subsequent updates. I'd also include the current user in the update so that you can easily retrieve the last row inserted by that user. So.... The 1st selection would do the insert as you have it, then immediately after the insert... do something like...
dim rowID as long
rowID = (create a function that gets the Max(ID) from that table for the current user)
Then, for each new selection for the current user for the current session, instead of using INSERT.. use UPDATE...
Something like...
strSQL = "UPDATE tblScenario SET scenarioPlatform = "'" & rtrim(plat) & "' WHERE ID = " & rowID

hth,
..bob

 

[alexius]

Thanks so much for your answer, Bob.

I updated the function by adding the step for with finding the last row (rowID) and changed the strSQL to UPDATE.

Private Sub CreatePlatform(plat As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim rowID As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("tblScenario")

rowID = rs.RecordCount

strSQL = "UPDATE tblScenario SET scenarioPlatform = " & RTrim(plat) & " WHERE ID = " & rowID & ""

CurrentDb.Execute (strSQL)

End Sub

When I go into debug mode, all my values are there (rowID=17 and plat=3, for example). But when I get to CurrentDb.Execute(strSQL) I get the "Too few parameters" error. I'm pretty new to this, so hopefully I'm just missing something simple...?

Sorry for the late response, I was out of town. Thanks again!

 

[boblarson]

If plat is text then it would need quotes:

[FONT=Courier New]strSQL = "UPDATE tblScenario SET scenarioPlatform = " [B][COLOR=red]& Chr(34) [/COLOR][/B]& RTrim(plat) [B][COLOR=red]& Chr(34)[/COLOR][/B] & " WHERE ID = " & rowID & ""[/FONT]

 

[alexius]

Thanks for your response, Bob L.

I tried it with various versions of the double quotes, but each time I got "Run-time error '3075': Syntax error (missing operator) in query expression ..."

This might be because it's an integer (in the Platforms table I have each platformName with a platformID primary key, which is what I'm using).

I tried to simplify it:

strSQL = "UPDATE tblScenario SET scenarioPlatform = " & plat & " WHERE ID = " & rowID & ""

But then it goes back to the "not enough parameters" error, even though in debug I can hover over the variables plat and rowID and they contain the correct values.

Thanks!

 

[boblarson]

Do you have lookups defined at table level?

Also, you don't need the & "" at the end of the statement.

 

[BobMcClellan]

Thanks so much for your answer, Bob.

I updated the function by adding the step for with finding the last row (rowID) and changed the strSQL to UPDATE.

Private Sub CreatePlatform(plat As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim rowID As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("tblScenario")

rowID = rs.RecordCount

strSQL = "UPDATE tblScenario SET scenarioPlatform = " & RTrim(plat) & " WHERE ID = " & rowID & ""

CurrentDb.Execute (strSQL)

End Sub

When I go into debug mode, all my values are there (rowID=17 and plat=3, for example). But when I get to CurrentDb.Execute(strSQL) I get the "Too few parameters" error. I'm pretty new to this, so hopefully I'm just missing something simple...?

Sorry for the late response, I was out of town. Thanks again!

Hello Alexius,
.. couple things...
1st, Never use rowID = rs.RecordCount. You are just asking for trouble. If rows are deleted, The Max(rowID) will not equal the recordcount.

2nd.. with regard to the SQL
You have ..
strSQL = "UPDATE tblScenario SET scenarioPlatform = " & RTrim(plat) & " WHERE ID = " & rowID & ""

if plat is text try this....
strSQL = "UPDATE tblScenario SET scenarioPlatform = "'" & rtrim(plat) & "' WHERE ID = " & rowID

if plat is NOT text try this....
strSQL = "UPDATE tblScenario SET scenarioPlatform = " & plat & " WHERE ID = " & rowID

As Bob L pointed out, you only need to use quotes if you are passing a text string.
(Just remember to use the spacing to keep your variables separated from your sql)

hth,
..bob

 

[alexius]

Thanks for your responses.

@boblarson: The code I posted is what I have, so I'm guessing I don't have the lookups defined at table level. I'm not sure what those look like, do you mind explaining it further?

@BobMcClellen: I don't foresee any records being deleted, but just in case, do you have an alternative you like to use instead of the = rs.RecordCount?

Thanks guys for the syntax help, and for any advice!

 

[BobMcClellan]

Thanks for your responses.

@boblarson: The code I posted is what I have, so I'm guessing I don't have the lookups defined at table level. I'm not sure what those look like, do you mind explaining it further?

@BobMcClellen: I don't foresee any records being deleted, but just in case, do you have an alternative you like to use instead of the = rs.RecordCount?

Thanks guys for the syntax help, and for any advice!

If you are on the form... and you want to update the existing row.... simply use
RowID = me.rowID
IF you do a new insert... and want to get the new rowID that was created when you
did the insert then, immediately after the insert
do something like... RowID = getRowID()
In other words, create a function that returns the Max(RowID)
It's been a while since I've written a vba function but I just opened an .mdb that I still use and created this for one. It returns the max Project ID from a table named Projects.

Function GetRowID()
Dim s As String
Dim rs As Recordset
s = "Select Max([Project ID]) as MaxID From Projects"
Set rs = CurrentDb.OpenRecordset(s)

GetRowID = rs.Fields("MaxID")
rs.Close
Set rs = Nothing

End Function

if you paste that code into a module using your table and RowID,
then hit Ctrl G to go to the immediate window,.. and type
? GetRowID it will return the Max(RowID)

So... this would get the Max Row ID for you....
RowID = getRowID()
hth,
..bob

 

[boblarson]

Thanks for your responses.

@boblarson: The code I posted is what I have, so I'm guessing I don't have the lookups defined at table level. I'm not sure what those look like, do you mind explaining it further?

See here:

@BobMcClellen: I don't foresee any records being deleted,

It isn't just deletions. Autonumbers are NOT guaranteed to go in sequential order, nor are they guaranteed to be a positive number. They are only guaranteed to be a UNIQUE number (and even that fails sometimes if the seed gets off).

 

[alexius]

Thank you all so much for your help, I finally got it to work. For reference, I'll put the whole code here:

Option Compare Database

Private Sub btnPlatformNext_Click()

Dim intPlatform As Integer

cmbPlatform.SetFocus
intPlatform = cmbPlatform.Value

Call CreatePlatform(intPlatform)

End Sub

Private Sub CreatePlatform(plat As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim rowID As Long

'Use the rowID to update the table

Set db = CurrentDb
Set rs = db.OpenRecordset("tblScenario")

rowID = GetRowID()

strSQL = "UPDATE tblScenario SET scenarioPlatform = " & plat & " WHERE scenarioID = " & rowID

CurrentDb.Execute (strSQL)

End Sub

'Get rowID function

Function GetRowID()
Dim s As String
Dim rs As Recordset
s = "Select Max(scenarioID) as MaxID From tblScenario"
Set rs = CurrentDb.OpenRecordset(s)

GetRowID = rs.Fields("MaxID")
rs.Close
Set rs = Nothing

End Function