how to send data to a table by using command button

[rdade22]

Hi,
I'm new so try to bare with me. I'm using access 2000 and I created a database where the user is prompted to click on a command button and the info that was put on the form is sent to a table. This is what I have so far:

Private Sub cmdadd_Click()
Dim db As Database
Dim rst As Recordset
Dim strCriteria As String
Dim intPointer As Integer
 
Set dbspushdb = OpenDatabase("push.mdb")
Set rst = dbspushdb.OpenRecordset("Projects", dbOpenDynaset)
 
' Ensure that key fields are not empty.
 
  If kittyID = "" Or kittyID = " " Then
        MsgBox prompt:="Please Input Kittyhawk Task Number!", _
        buttons:=vbOKOnly + vbExclamation, _
        title:="Vailidation Error"
    kittyID.SetFocus
    Exit Sub
  End If
 
  With rst
 
  ' If key field has been updated, add NEW record else ...
        If .Locked = False Then
            .AddNew
        Else
' ... find existing record.
            strCriteria = "[kittyID] = " & Projects & ""
            .FindNext strCriteria
            .Edit
       End If
 
' Update table with form details
    .Edit
    .Fields("kittyID") = kittyID
    .Fields("cboStore") = cboStore
    .Fields("cboManager") = cboManager
    .Fields("ProjectTitle") = ProjectTitle
    .Fields("Jdial") = JdialNumber
    .Field("ProjectDescription") = ProjectDescription
    .Fields("StartDate") = StartDate
    .Fields("EndDate") = EndDate
    .Fields("ProjectOffice") = ProjectOffice
    .Update
  End With
 
  With Projects
' Refresh List.
    .Requery
    ' Ensure that the record selected in list matches current
    For intPointer = 0 To .ListCount - 1
        If .Column(0, intPointer) = rst.Fields("Projects") Then
            .Selected(intPointer) = True
            Exit For
        End If
    Next intPointer
    .SetFocus
  End With
 
  rst.Close
 
  Projects.Locked = True
 

Please help.

[Rabbit]

If the form's record source is the table and it's open as a dynaset, then you don't have to tell it to update the table with the information. It'll do that automatically as you enter information.

[MMcCarthy]

' Update table with form details
 .Edit ' this overrides the .AddNew statement - remove it
.Fields("kittyID") = Me.kittyID
    .Fields("cboStore") = Me.cboStore
    .Fields("cboManager") = Me.cboManager
    .Fields("ProjectTitle") = Me.ProjectTitle
    .Fields("Jdial") = Me.JdialNumber
    .Field("ProjectDescription") = Me.ProjectDescription
    .Fields("StartDate") = Me.StartDate
    .Fields("EndDate") = Me.EndDate
    .Fields("ProjectOffice") = Me.ProjectOffice
    .Update
  End With
 

The Me. designates fields on the form. Make sure the control names are correct.

  With Me.Projects ' assume this is a listbox control name
' Refresh List.
    .Requery
    ' Ensure that the record selected in list matches current
    For intPointer = 0 To .ListCount - 1
 ' if column0 is the bound column then
        If .ItemData(intPointer) = rst.Fields("Projects") Then
            .Selected(intPointer) = True
            Exit For
        End If
    Next intPointer
    .SetFocus
  End With
 
  rst.Close
Set rst = Nothing
Set dbspushdb = Nothing
 
  Projects.Locked = True
 

[NeoPa]

If the form's record source is the table and it's open as a dynaset, then you don't have to tell it to update the table with the information. It'll do that automatically as you enter information.

As Rabbit says, while it's possible to do all sorts of complicated stuff in VBA in Access, you really would be better advised (unless you know a reason why not, that you've not shared with us) to create a form bound to the underlying recordset (table) and letting Access handle the updates automatically.

[Phille]

Otherwise a simple append query that grabs the info directly from the control will do.

[NeoPa]

As in post #4, it's possible, but why complicate your life doing it the complicated way when Access provides a natural way of doing it :confused:

[rdade22]

As in post #4, it's possible, but why complicate your life doing it the complicated way when Access provides a natural way of doing it :confused:

I have tried it that way but nothing happen. I have a submit button on the form once it is click it does nothing to enter records into the table. I even used the command button wizard but still no luck. I have ask the same question in a different form and some users gave me hints about diming a database and things of that sort which I knew nothing about. So I came here and from that this forum has been very helpful to me with recent questions and quick responses. This is what I have now:

 Dim db As Database
Dim rst As Recordset
Dim strCriteria As String
Dim intPointer As Integer
 
Set dbspushdb = OpenDatabase("push.mdb")
Set rst = dbspushdb.OpenRecordset("Projects", dbOpenDynaset)
  With rst
 
        If .Locked = False Then
            .AddNew
        Else
       End If
  ' Update table with form details
    .Fields("kittyID") = kittyID
    .Fields("cboStore") = cboStore
    .Fields("cboManager") = cboManager
    .Fields("ProjectTitle") = ProjectTitle
    .Fields("Jdial") = JdialNumber
    .Field("ProjectDescription") = ProjectDescription
    .Fields("StartDate") = StartDate
    .Fields("EndDate") = EndDate
    .Fields("ProjectOffice") = ProjectOffice
    .Update
  End With
 
  rst.Close
  Set rst = Nothing
  Set dbspushdb = Nothing
 
  Projects.Locked = True 

Where do I go from here?

[NeoPa]

I would suggest trying it the normal way.
Get the Forms wizard to help you create a form bound to your table or query.
Almost all of the extra stuff is then unnecessary.
When you look at what it creates for you, then you will understand better what I'm talking about.

[rdade22]

I would suggest trying it the normal way.
Get the Forms wizard to help you create a form bound to your table or query.
Almost all of the extra stuff is then unnecessary.
When you look at what it creates for you, then you will understand better what I'm talking about.

I've tried the wizard command button again. It gives the error that "It can't go to specified record". So what I done to hopefully try to fix the problem was I compact and repair database. Do you think this will help me?

[NeoPa]

I don't know if you're replying to my post or whether you just clicked on the Reply button to post.
If you haven't read my last post I suggest you do so, or at least let me know that you're not interested in my continued contributions.
If you are actually replying to my post, then I suggest you check it again, as it says to try the Form wizard NOT the command button control wizard.

Please let me know how you get on (or alternatively let us know if you need help to do this).