Database Help 2

[Cballe]

Hi Guys,

I would appreciate it very much if someone could help me with the following:

I have created a database application by making use of an Ms Access database format (not ODBC). The application consist of two forms:

fMain
fTest
Database Name: UserInfo.mdb

The first form asks the user his personal information like, Name, Surname etc. The user enter his details at the first form (and do not update the database yet) and click on a proceed button to go to the second form. At the second form the user complete a test. After the test is completed, he clicks on a button, btnCompute. The result of the the test are displayed by making use of a label, lblResult.

Now here is were I need help: When the user clicked on the compute button, the results that are displayed by the lblResult (label), must also be written to the database. The database must then be updated so that the final data in the database should look like this:

Name: James
Surname: Bond
Gender: Male
Test Result: 78%

NOTE: As you noticed, the data of the first form (fMain) are part of the data (test result) of the second form (fTest). I need some help on how to do this.

The second problem that I am having is the following:

As part of data in my database, I want to write the system time & date at the moment the update button is clicked. (If possible the system date must also be written to the database) – all together with the details of the user that he have entered at the first form. For example:

Name: James
Surname: Bond
Gender: male
Test Result: 78%
Time test was completed: 09:15
Date: 06/08/2002

I thought about something like this:
Create a Date/Time field (eg SystemTime) in the desired table.

-Create a recordset in code
-Use the .AddNew methode to create a new entry
-Set the desired field to Now(): .Fields("SystemTime&quot.Value = Now()
-Update recordset

I am not really sure how to implement this – would appreciate some ideas…

Thanks guys for your time…
Regards
C-Power

 

[CajunCenturion]

In the main form, create your connection, and make the connection public so that it can be accessed from the "Test" form. Since you can access the controls from the first form, you might try something like the following in the btnCompute Click Event

Sub btnCompute_Click

Dim CurrDateTime as Date
Dim NewRec as ADODB.RecordSet
Dim TestRes as Long ??

CurrDateTime = Now()
TestRes = <compute test results>
Set NewRec = New ADODB.RecordSet
With NewRec
.ActiveConnection = frmMain.fADO_Connect
.AddNew
.Fields(&quot;Name&quot = frmMain.txtFirstName.Text
.Fields(&quot;SurName&quot = frmMain.txtSurName.Text
<populate the rest of the fields>
.Fields(&quot;Results&quot = TestRes
.Fields(&quot;TimeOfTest&quot = CurrDateTime
.Update
End With
Set NewRec = Nothing

End Sub
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[100watt]

You could do this a couple ways.
First you could set up the initial form so that as soon as the text boxes lose focus to save the control text to a global variable, which can be seen from all forms in the project. Or if you just hide the form you can still reference all the controls on the form, you just need to specify the form before the control name or use the &quot;with&quot; statement.

Examples:

Form1.Text1.Text

OR

with Form1
.Text1.text
End With

As for the capturing the date and time of the update, just add a datetime field to the database. Remember that the date in Access SQL is treated different from dates in other database applications. The date is surrounded by number signs.

Your query might look something like this:

INSERT INTO Info(Name,Surname,Gender,Result,UpdateDate)
VALUES('&quot; & strName & &quot;','&quot; & _
strSurname & &quot;','&quot; & _
strGender & &quot;','&quot; & _
strResult & &quot;',#&quot; & _
format(Now,&quot;yyyy-mm-dd hh:mm:ss&quot & &quot;#)&quot;

Now being the current date and time function for visual basic.

 

[Cballe]

HI there guys,

I made use of the suggestions that you gave and also had to made some changes to the code. The only problem I still get is the one below....any help? I have added my code...

Error message: Operation is not allowed when object is closed

Private Sub Command1_Click()

Dim CurrDateTime As Date
Dim NewRec As ADODB.Recordset
CurrDateTime = Now()

Set NewRec = New ADODB.Recordset
With NewRec
Set .ActiveConnection = fMain.datPrimaryRS.Recordset.ActiveConnection
.AddNew
.Fields(&quot;Name&quot = fMain.txtFields(0).Text
.Fields(&quot;Surname&quot = fMain.txtFields(1).Text
.Fields(&quot;Test_Results&quot = fTest.Label1.Caption
.Fields(&quot;Date&quot = CurrDateTime
.Update
End With
Set NewRec = Nothing
End Sub