adding date from a form to a table ( or two )

[stuartam]

i am still very new to all this so please forgive me.

i have a form that gets data from a table and displays it within the form.

what i want it to do before it saves the new data over the original in the record, i want it to copy and paste 3 fields to a 2nd table.

here is the code for the save button ( it just saves the content of a text box on the form for the moment, i just want to see if i can get it to connect to the table and add a record )

Private Sub save_record_Click()
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim rs As ADODB.Recordset
    Dim Datei As String
    
    Datei = currentdate
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "history", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("date") = "Datei"
        .Update ' update the table
        .Close ' close the recordset connection
    End With

End Sub

but it dosent work all i get is an error message:

Error accessing file. Network connection may have been lost.

what am i doing wrong?

i have attached a copy of the DB if needed.

best regards

 

[pono1]

Stuart,

Do a Google search (or perhaps even a search here) on your error message and you will hit gold.

Regards,
Tim

 

[dan-cat]

You're not too far off but here are a couple of pointers...

Dim Datei As String

why declare this as a string if the value is going to be a date. I would change it to...

Dim Datei As Date

With this line

Set rs = New ADODB.Recordset

you have created a fresh recordset object so no need to declare it with

Dim rs As ADODB.Recordset

Change this line

Datei = currentdate

to

Datei = Date

Here is the line that you're having most trouble with...

.Fields("date") = "Datei"

Never,never,never call a field 'Date'. This is a reserved word and will cause you all sorts of problems down the line. Learn all the reserved words and avoid using them as field names - you'll be sorry later on if you don't.
You also have your date variable surrounded with "". This is telling ADO to actually update your field with the value "Datei" not the actual current date.
I would change this line to ....

.Fields("myDate") = Datei

making sure that you change the fieldname 'Date' in your 'history' table to something else - in this case 'myDate'.

If this was a first go then you did very well

 

[pono1]

Or is Dan-Cat on to something? Let us know... the Access Know-It-All award (a used keyboard with cookie crumbs lodged between the keys) hinges on it.

Regards,
Tim

 

[dan-cat]

Or is Dan-Cat on to something? Let us know... the Access Know-It-All award (a used keyboard with cookie crumbs lodged between the keys) hinges on it.

Regards,
Tim

hehehe chocolate hob-nob crumbs me-hopes!

 

[Access_Person]

Ok, I had the exact same issue to deal with and decided to search, following the ubiquitous advice of forum goers fed up with repeat questions. I used the above code (with a few modifications) but can't get it to work. Any help would be appreciated: keep in mind that those cookie crumb infested keyboards don't come cheap.

I get an "object required" error in the highlighted line.

Private Sub btnAdd_Click()

    Dim cn As Connection
    Dim CompanyName As String
    
    CompanyName = Me.Company_Name.Value
    
    'open connection
    
    Set cn = CurrentProject.Connection
    
    'initialize the recordset object
    
[color=green]    Set rs = NewADODB.Recordset[/color]
    
    'using the recordset object
    With rs
        .Open "Contracting Agency List", cn, adOpenStatic, adLockPessimistic 'open it
        .AddNew
        .Fields("Vendor") = CompanyName
        .Update 'update the table
        .Close 'close the recordset connection
    End With
    
End Sub

 

[pono1]

Fellow Access Person,

I see at least two things:

The following line needs a space.

Set rs = NewADODB.Recordset

and should be

Set rs = New ADODB.Recordset.

And I don't see the recordset object declared anywhere in the procedure.

Dim rs as Adodb.Recordset

Otherwise, best I can tell, all systems are go.

Regards,
Tim

 

[stuartam]

it all worked GREAT, thanks SO SO SO much for all the help.

ps that was my 1st go i used a bit of code i found and modded it to suite my needs, so i guess it wasnt my 1st real bit of code but it was close enough.

best regards

 

[stuartam]

its been working great, thanks again.

but now i need to add some more features to it and i cant get it to work.

i want it to write the data to the table it is currently but also i want it to write a few selected bits to another table ( i know its duplicating data, but its only a small bit )

here is my current code:

Private Sub save_Click()
On Error GoTo Err_save_Click
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = asuid
    vbpackref = Text6
    vbasstate = asstate
    vbasdate = Dateinputbox
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With
    
Exit_save_Click:
    Exit Sub

Err_save_Click:
    MsgBox Err.Description
    Resume Exit_save_Click
    
End Sub

the other table is called: TBLEleSesTrk

and the data i want to store is:

vbasuid in the field UserID
vbpackref in the field PackRef

all help would be GREATLY welcomed.

 

[mohsinhq]

give this a whirl, just add on to the end of your current code for cmdsave

Set rs = db.OpenRecordset("TBLEleSesTrk", dbOpenTable)
With rs
.AddNew
.Fields("UserID") = vbasuid
.Fields("PackRef") = vbpackref
.Update
End With
rs.Close
Set rs = Nothing

 

[stuartam]

thanks i'll give it a go.

 

[stuartam]

thanks for the reply, i tryed that and i get an 'object required' error, here is the full code.

Private Sub save_Click()
On Error GoTo Err_save_Click
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = asuid
    vbpackref = Text6
    vbasstate = asstate
    vbasdate = Dateinputbox
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With
    
    Set rs = db.OpenRecordset("TBLPacks", dbOpenTable)
    With rs
        .Edit
        .Fields("CurPackState") = vbasstate
        .Update
    End With
    rs.Close
    
Exit_save_Click:
    Exit Sub

Err_save_Click:
    MsgBox Err.Description
    Resume Exit_save_Click
    
End Sub

i want it to save the content of the 'asstate' text box to the 'CurPackState' field of the current record.

thanks for all the help this is a great forum.

best regards

 

[stuartam]

problem solved, i used this code:

    With Recordset
    .Edit
    !CurPackState = asstate
    .Update
    End With