Add Record VIA Recordset Code

[SDS100UK]

Hi,

Access XP

I have a very basic understanding of VBA and I know what I want it to do but have not really the first idea how to do it.

I have a form with a subform on it. The subform is in Datasheet view. The parent form has a button on it that when it is clicked i need to add the date and time (2 seperate fields) onto the subform.

I could do it with a text box, but i dont want to overwrite any previous data, so adding records to this table seems to be the solution.

I have looked for an answer in the forums but was unable to.

Please can anyone help me?

Many thanks in advance

Steven

 

[Craig0201]

What's wrong with a ggod old INSERT SQL statement

INSERT INTO
YourTable
(
YourDateField,
YourTimeField
)
VALUES
Date(),
Format(Now(),"hh:nn:ss")

combined with DoCmd.OpenQuery "Whatever you've called the above query"

Craig

 

[scriverb]

Here is recordset code if that is still what you want:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("[i][red]yourtablename[/red][/i]", dbOpenRecordset)
rs.AddNew
rs("[i][red]fieldname1[/red][/i]") = [i]value[/i]
[green][i]. . . duplicate above line for all fields. . [/i][/green]
rs.Update
rs.close
db.close

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SDS100UK]

Cheers Scriverb,

I imagined this is what i wanted, bt when i try it i get an error message that says -
"Run time error
invalid argument"

I have copied my code in to this message for you.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblDateAndTimeStamp",dbOpenRecordset)


rst.AddNew
rst("CustomerRef") = Me.CustomerReference
rst("despatchtixdate") = Date
rst("despatchtixtime") = Now()
rst.Update
rst.Close
dbs.Close


hope you can see where i am going wrong.

Many thanks


Steven

 

[scriverb]

Sorry about the typo. My fault.

 Set rst = dbs.OpenRecordset("tblDateAndTimeStamp",[red]dbOpenRecordset[/red])

Change to the following:

[code] Set rst = dbs.OpenRecordset("tblDateAndTimeStamp",dbOpenDynaset)

This should work.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SDS100UK]

Sriverb----youre a STAR


Thanks a million!

Cheers


Steven

 

[goaway1234]

dbOpenRecordset is not a constant. If you don't have 'Option Explicit' at the top of your code, then a new variable named dbOpenRecordset is being created and given the default value, Empty. Try using dbOpenDynaset insead.