Saving a form variable to a table

[emajka21]

I am trying to create a database that saves marathon times to a database. The way the times are marked are in minutes and seconds. There are 8 sections to the marathon and the times will have to be added together later on (another problem entirely that I will deal with later. :) After researching the best way to do that I found that it is recommended that I convert the minutes to seconds and add the rest of the seconds together and just store the time as an integer. Well I created a form and for time I created two combo boxes, 1 for minutes and 1 for seconds. My plan was to add a button and when that is clicked it will create a new variable(totalTime) and multiply the minutes by 60 and then add the seconds. Now my question is how to get that variable's data into my table? I tried an insert into and it gave me an error. Is the insert into the best route or is there another path I should try?

I feel like I am missing something easy and any nudge in the right direction would be much appreciated.

[ADezii]

I am trying to create a database that saves marathon times to a database. The way the times are marked are in minutes and seconds. There are 8 sections to the marathon and the times will have to be added together later on (another problem entirely that I will deal with later. :) After researching the best way to do that I found that it is recommended that I convert the minutes to seconds and add the rest of the seconds together and just store the time as an integer. Well I created a form and for time I created two combo boxes, 1 for minutes and 1 for seconds. My plan was to add a button and when that is clicked it will create a new variable(totalTime) and multiply the minutes by 60 and then add the seconds. Now my question is how to get that variable's data into my table? I tried an insert into and it gave me an error. Is the insert into the best route or is there another path I should try?

I feel like I am missing something easy and any nudge in the right direction would be much appreciated.

1. Post all relevant information such as: Table Structure, Field definitions including Data Types, Names, etc. especially the [Minutes] and [Seconds] Fields.
2. What's your Form Name, Combo Box Names, etc.
3. Post the INSERT INTO code that is not working.
4. How are the Minutes and Seconds data stored? Are there Minutes and Seconds Fields for each section of the Marathon?
5. I think you got the idea...

[emajka21]

The Race table is BibNum, RaceNum, Time (an integer).

On the form (Form_Race) I have a combo box with the race number (values are stored in row source. Bib Number, a text field.
Time: there is a minutes combo box from 20-180 (stored in row source) and a Seconds Combo box (0-59). These are not tied to a control because I want to use them to calculate a new variable.

This is my code:
Dim totalTime As Integer
totalTime = cboMinutes * 60 + cboSeconds

INSERT INTO Race (BibNum, RaceNum, Time) VALUES ([Form_Race.BibNum],[Form_Race.RaceNum],totalTime)

Error message (Stops on Race) Compile Error: Expected: end of statement

[ADezii]

The Race table is BibNum, RaceNum, Time (an integer).

On the form (Form_Race) I have a combo box with the race number (values are stored in row source. Bib Number, a text field.
Time: there is a minutes combo box from 20-180 (stored in row source) and a Seconds Combo box (0-59). These are not tied to a control because I want to use them to calculate a new variable.

This is my code:
Dim totalTime As Integer
totalTime = cboMinutes * 60 + cboSeconds

INSERT INTO Race (BibNum, RaceNum, Time) VALUES ([Form_Race.BibNum],[Form_Race.RaceNum],totalTime)

Error message (Stops on Race) Compile Error: Expected: end of statement

This will do the trick for you.

Dim totalTime As Integer, strSQL As String
Dim MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Race", dbOpenDynaset)
 
totalTime = (Nz(Me![cboMinutes]) * 60) + Nz(Me![cboSeconds])
 
With MyRS
  .AddNew
    ![BibNum] = Me![BibNum]
    ![RaceNum] = Me![RaceNum]
    ![Time] = totalTime
  .Update
End With
 
MyRS.Close
 
Me.Requery

[emajka21]

Thank you so much for trying to help me. I typed in what you wrote and it gave me a new error:
Compile Error: User-defined type not defined

It highlighted the 'Dim MyDB As DAO.Database' part

[ADezii]

Thank you so much for trying to help me. I typed in what you wrote and it gave me a new error:
Compile Error: User-defined type not defined

It highlighted the 'Dim MyDB As DAO.Database' part

Your Reference to the Microsoft Data Access Objects is not set. If you are using ADO, then the syntax should be:

Dim MyDB As ADODB.Database, MyRS As ADODB.Recordset