By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,899 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,899 IT Pros & Developers. It's quick & easy.

how to send data to a table by using command button

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdadd_Click()
  2. Dim db As Database
  3. Dim rst As Recordset
  4. Dim strCriteria As String
  5. Dim intPointer As Integer
  6.  
  7. Set dbspushdb = OpenDatabase("push.mdb")
  8. Set rst = dbspushdb.OpenRecordset("Projects", dbOpenDynaset)
  9.  
  10. ' Ensure that key fields are not empty.
  11.  
  12.   If kittyID = "" Or kittyID = " " Then
  13.         MsgBox prompt:="Please Input Kittyhawk Task Number!", _
  14.         buttons:=vbOKOnly + vbExclamation, _
  15.         title:="Vailidation Error"
  16.     kittyID.SetFocus
  17.     Exit Sub
  18.   End If
  19.  
  20.   With rst
  21.  
  22.   ' If key field has been updated, add NEW record else ...
  23.         If .Locked = False Then
  24.             .AddNew
  25.         Else
  26. ' ... find existing record.
  27.             strCriteria = "[kittyID] = " & Projects & ""
  28.             .FindNext strCriteria
  29.             .Edit
  30.        End If
  31.  
  32. ' Update table with form details
  33.     .Edit
  34.     .Fields("kittyID") = kittyID
  35.     .Fields("cboStore") = cboStore
  36.     .Fields("cboManager") = cboManager
  37.     .Fields("ProjectTitle") = ProjectTitle
  38.     .Fields("Jdial") = JdialNumber
  39.     .Field("ProjectDescription") = ProjectDescription
  40.     .Fields("StartDate") = StartDate
  41.     .Fields("EndDate") = EndDate
  42.     .Fields("ProjectOffice") = ProjectOffice
  43.     .Update
  44.   End With
  45.  
  46.   With Projects
  47. ' Refresh List.
  48.     .Requery
  49.     ' Ensure that the record selected in list matches current
  50.     For intPointer = 0 To .ListCount - 1
  51.         If .Column(0, intPointer) = rst.Fields("Projects") Then
  52.             .Selected(intPointer) = True
  53.             Exit For
  54.         End If
  55.     Next intPointer
  56.     .SetFocus
  57.   End With
  58.  
  59.   rst.Close
  60.  
  61.   Projects.Locked = True
  62.  
Please help.
Feb 2 '07 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,364
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.
Feb 2 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. ' Update table with form details
  2. .Edit ' this overrides the .AddNew statement - remove it
  3. .Fields("kittyID") = Me.kittyID
  4.     .Fields("cboStore") = Me.cboStore
  5.     .Fields("cboManager") = Me.cboManager
  6.     .Fields("ProjectTitle") = Me.ProjectTitle
  7.     .Fields("Jdial") = Me.JdialNumber
  8.     .Field("ProjectDescription") = Me.ProjectDescription
  9.     .Fields("StartDate") = Me.StartDate
  10.     .Fields("EndDate") = Me.EndDate
  11.     .Fields("ProjectOffice") = Me.ProjectOffice
  12.     .Update
  13.   End With
  14.  
The Me. designates fields on the form. Make sure the control names are correct.

Expand|Select|Wrap|Line Numbers
  1.   With Me.Projects ' assume this is a listbox control name
  2. ' Refresh List.
  3.     .Requery
  4.     ' Ensure that the record selected in list matches current
  5.     For intPointer = 0 To .ListCount - 1
  6. ' if column0 is the bound column then
  7.         If .ItemData(intPointer) = rst.Fields("Projects") Then
  8.             .Selected(intPointer) = True
  9.             Exit For
  10.         End If
  11.     Next intPointer
  12.     .SetFocus
  13.   End With
  14.  
  15.   rst.Close
  16. Set rst = Nothing
  17. Set dbspushdb = Nothing
  18.  
  19.   Projects.Locked = True
  20.  
Feb 3 '07 #3

NeoPa
Expert Mod 15k+
P: 31,471
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.
Feb 4 '07 #4

P: 22
Otherwise a simple append query that grabs the info directly from the control will do.
Feb 6 '07 #5

NeoPa
Expert Mod 15k+
P: 31,471
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:
Feb 7 '07 #6

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1.  Dim db As Database
  2. Dim rst As Recordset
  3. Dim strCriteria As String
  4. Dim intPointer As Integer
  5.  
  6. Set dbspushdb = OpenDatabase("push.mdb")
  7. Set rst = dbspushdb.OpenRecordset("Projects", dbOpenDynaset)
  8.   With rst
  9.  
  10.         If .Locked = False Then
  11.             .AddNew
  12.         Else
  13.        End If
  14.   ' Update table with form details
  15.     .Fields("kittyID") = kittyID
  16.     .Fields("cboStore") = cboStore
  17.     .Fields("cboManager") = cboManager
  18.     .Fields("ProjectTitle") = ProjectTitle
  19.     .Fields("Jdial") = JdialNumber
  20.     .Field("ProjectDescription") = ProjectDescription
  21.     .Fields("StartDate") = StartDate
  22.     .Fields("EndDate") = EndDate
  23.     .Fields("ProjectOffice") = ProjectOffice
  24.     .Update
  25.   End With
  26.  
  27.   rst.Close
  28.   Set rst = Nothing
  29.   Set dbspushdb = Nothing
  30.  
  31.   Projects.Locked = True 
Where do I go from here?
Feb 7 '07 #7

NeoPa
Expert Mod 15k+
P: 31,471
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.
Feb 7 '07 #8

P: 9
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?
Feb 7 '07 #9

NeoPa
Expert Mod 15k+
P: 31,471
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).
Feb 7 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.