Add record to Table from Form (1 Viewer)

Jack Hammmer · May 25, 2009

I need a button that will be able to create a new row and add certain fields to the table from the form.

I just need the 'Puchase' button to add;
Service ID
Customer ID
Services ID
Order Date

From the form to the table and the 'Remove' button to take the selected Purchased product from the second List Box away from the table (Delete record.

I have also included my database as an attachment in-case the information I have given is not enough: I really need help as I don't understand how to do this and my project is already overdue!

Thank-you in advance...

pbaldy · May 25, 2009

This type of thing?

http://www.baldyweb.com/MultiselectAppend.htm

Jack Hammmer · May 25, 2009

Yes; something very similar to what you have done there. Although ashamed to admit it I do not know VBA or even VB at all and can barely understand what you have managed to do: By barely I mean I don't understand. Though if you could explain it to me I would be extremely grateful. I have had enough of searching Google and coming up with nothing

.

Jack Hammmer · May 25, 2009

pbaldy said:
This type of thing?

http://www.baldyweb.com/MultiselectAppend.htm

Thank you very much! I am very grateful...

Though I would be more grateful if you could help me with the remove button; this could either remove the last entry on the form or remove the selected row from the List Box. Again I do not know how to do this...

Although I have stolen a lot of your VBA (I hope you don't mind) and trial and errored my way until I got it to work. I am not at all sure if my custom If And works?

Once again thanks PBaldy!

(N.B/ I know that using the smiley makes me look like a tweleve year old but I am very grateful)

Code:

Private Sub Add_Click()

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tbl_Services", dbOpenDynaset, dbAppendOnly)

    'Make sure a valid Customer ID has been entered
  If Not IsNumeric(Me.CID) And Me.CID = 0 Then
    MsgBox "Must enter a valid Customer ID."
    Exit Sub
  End If

    'Make sure a selection has been made
  If Me.SID.ItemsSelected.Count = 0 Then
    MsgBox "Must select an item."
    Exit Sub
  End If

    'Add selected value(s) to table
  Set ctl = Me.SID
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs![Customer ID] = Me.CID
    rs![Services ID] = ctl.ItemData(varItem)
    rs![Order Date] = Me.DateTime
    rs.Update
  Next varItem

    'Refresh queries and display
    Me.Requery
    Me.Refresh

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Sub

pbaldy · May 25, 2009

Of course I don't mind. If I didn't mean for you to use the code, I wouldn't have posted it.

I think you want this:

If Not IsNumeric(Me.CID) OR Me.CID = 0 Then

as it could never be both non numeric and equal to zero. If you're trying to delete selected items, you could execute DELETE SQL inside a similar loop:

db.Execute "DELETE * FROM TableName..."

building a WHERE clause that narrows the delete down to the specific record.

Add record to Table from Form (1 Viewer)

Jack Hammmer

Database n00b

Attachments

pbaldy

Wino Moderator

Jack Hammmer

Database n00b

Jack Hammmer

Database n00b

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread