Update a table from a command button without binding to a form?

  • Thread starter Thread starter Noozer
  • Start date Start date
N

Noozer

This is the last item I need to finish up an MS Access project I'm working
on...

I have a form. On this form is a listbox populated with a number of items.
There is also an ADD button. There is also a subform displaying the results
of a query.

When I click the ADD button, I want to take a piece of data from my form, a
piece of data from my list and add them to a table, then requery the
subform. I don't have anything bound directly to the table that I wish to
add to.

With the code below, assume that the data has already been gathered from the
form and listbox. How can I add this data to the table?

'Following code doesn't work (obviously) but gives idea of what I'm
trying to do
Private Sub cmdAdd_Click()

'Add a new record to the AreaCrewLink table
With Tables![CrewAreaLink]
.AddNew
!CrewKeyLink = 999
!AreaKeylink = 999
!Note = "Test Note"
.Update
End With

'Requery subform to show added record.
sub_ChooseCrewsEdit.Requery
End Sub

.... I've seen examples of how to use DAO with VB, but since this is VBA
withing an Access database I was wondering if there was a simpler, more
direct method.

Thx!
 
CurrentDb().Execute "INSERT INTO " & _
"AreaCrewLink (CrewKeyLink, AreaKeylink, Note) " & _
"Values (999, 999, 'Test Note')", dbFailOnError
 
I knew that there had to be an easier way... Thanks muchly!

Douglas J. Steele said:
CurrentDb().Execute "INSERT INTO " & _
"AreaCrewLink (CrewKeyLink, AreaKeylink, Note) " & _
"Values (999, 999, 'Test Note')", dbFailOnError
When I click the ADD button, I want to take a piece of data from my
Click to expand...
form,
a
piece of data from my list and add them to a table, then requery the
subform. I don't have anything bound directly to the table that I wish to
add to.
Private Sub cmdAdd_Click()

'Add a new record to the AreaCrewLink table
With Tables![CrewAreaLink]
.AddNew
!CrewKeyLink = 999
!AreaKeylink = 999
!Note = "Test Note"
.Update
End With

'Requery subform to show added record.
sub_ChooseCrewsEdit.Requery
End Sub
Click to expand...
Click to expand...
 
Back
Top