add specified boxes to an existing table through vba 1

[Aleathiel]

Hi all,

I don't really like to bother other people with my problem but this one is too much for my knowledge and Im sure people out there know how to do this

I have a form that contains fields that I want to add in a table that is present as a sub_form on my form, the problem is i don't want to use the recordsource. I need the form without any kind of RecordSource so the people can add the field to the table ONLY when they click on the button, since I want all the information to stay there, I don't want to redo all the field each time.

The field I have are:
Date
Driver
Carrier
Picture
Drop
Acronym (this one need to be blank after the click)
BOL
Commentary (this one need to be blank after the click)

The form is name BOL and the table is also BOL

I'm pretty sure this can be done in VBA, but I don't have enough knowledge to decipher how to do it.

I hope I could

Thanks you in advance.

 

[AlexMidd]

I'm a little confused. Do you want to add the fields to the table or the data in the fields?

If the latter you would need to open a recordset object in VBA for the table or query concerned, then use AddNew and Update methods to add the data to the recordset.

Have fun! )

Alex Middleton

 

[Aleathiel]

Hi Alex, thanks for the response.

I'm looking to add the data to all the field in the table, I named the text box in the form the same name that the fields in the table. the recordset thing helped me a little, but I'm still lacking knowledge to be able to use it adequately, if you could provide me with an exemple of coding it would be really awesome.

Thanks a lot!!!

 

[AlexMidd]

Example, where the button to add the data is called 'cmdAddData':

Private Sub cmdAddData_Click
    Dim rstExample as DAO.Recordset
    Set rstExample = Currentdb.OpenRecordset("BOL")
    With rstExample
        .AddNew
        !Date = Me.txtDate
        !Driver = Me.txtDriver
        .... repeat for all required fields
        .Update
    End With
End Sub

Have fun! )

Alex Middleton

 

[Aleathiel]

Wow, its working like a charm!

Thanks a lot Alex!

My final code was:
-----------------------------
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim rstExample As DAO.Recordset
Set rstExample = CurrentDb.OpenRecordset("BOL")
With rstExample
.AddNew
!DATE = Me.txtDATE
!DRIVER = Me.txtDRIVER
!PICTURE = Me.txtPICTURE
!CARRIER = Me.txtCARRIER
!ACRONYM = Me.txtACRONYM
!DROP = Me.txtDROP
.Update
End With
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

----------------------------------

It is exactly what I wanted, only thing remaining is to find out how to empty the field "txtACRONYM" each time I click on the button and how to refresh the subform to show the new information I just added, I know I can do it by closing it and reopening it, but is there another way?

Thanks a lot!

Nathaniel Bell