By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

Saving a form variable to a table

P: 38
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.
May 10 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,628
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...
May 10 '07 #2

P: 38
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
May 10 '07 #3

ADezii
Expert 5K+
P: 8,628
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.
Expand|Select|Wrap|Line Numbers
  1. Dim totalTime As Integer, strSQL As String
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set MyRS = MyDB.OpenRecordset("Race", dbOpenDynaset)
  6.  
  7. totalTime = (Nz(Me![cboMinutes]) * 60) + Nz(Me![cboSeconds])
  8.  
  9. With MyRS
  10.   .AddNew
  11.     ![BibNum] = Me![BibNum]
  12.     ![RaceNum] = Me![RaceNum]
  13.     ![Time] = totalTime
  14.   .Update
  15. End With
  16.  
  17. MyRS.Close
  18.  
  19. Me.Requery
May 11 '07 #4

P: 38
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
May 11 '07 #5

ADezii
Expert 5K+
P: 8,628
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As ADODB.Database, MyRS As ADODB.Recordset
May 11 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.