Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Save from form to table

rcloughrclough Member Posts: 3
Hi, I am new with the VBA in Access and becoming very confused. I have a database I am working on for a business. With this I have a form that they can go into and create a bid for their customer. This form has about 25 - 20 listboxes that are set up for multiple selection. I also have a button so when they have the choices they want it will save to a table. This is where my problem is. I've worked a little in VB but not Access VBA. How can I get the choices from the form to save to the table? In my table I have the customer number and a field for each listbox. Also would I do this on the Save_Click event or at the beginnning? The final out come will be a form that they can print out to give to the customer, it will contain the options chosen, customer information and total.

Thanks for the advice.
rclough

Comments

  • dokken2dokken2 Member Posts: 532
    [b][red]This message was edited by dokken2 at 2006-6-23 5:4:21[/red][/b][hr]
    : Hi, I am new with the VBA in Access and becoming very confused. I have a database I am working on for a business. With this I have a form that they can go into and create a bid for their customer. This form has about 25 - 20 listboxes that are set up for multiple selection. I also have a button so when they have the choices they want it will save to a table. This is where my problem is. I've worked a little in VB but not Access VBA. How can I get the choices from the form to save to the table? In my table I have the customer number and a field for each listbox. Also would I do this on the Save_Click event or at the beginnning? The final out come will be a form that they can print out to give to the customer, it will contain the options chosen, customer information and total.
    :
    : Thanks for the advice.
    : rclough
    :


    there's several ways to do this with DAO, ADO, SQL,
    here's the two most common ones-
    [assume table TBL2 has text fields: LastName, Address ]

    you would need to run this code after the user has selected all their values, give them a SAVE or UPDATE button

    [code]
    Sub Sql_Insert()
    Dim Sql As String

    'sample data to insert
    'need to enclose strings within a double or single quote
    Dim Lname As String
    Dim Addr As String
    Lname = Chr(34) & "flintstone" & Chr(34)
    Addr = Chr(34) & "bedrock" & Chr(34)

    'sql to insert into table TBL2
    Sql = "INSERT INTO tbl2 ( LastName, Address )" & _
    "SELECT " & Lname & " AS Expr1, " & Addr & " AS Expr2;"
    '***run sql***
    DoCmd.RunSQL Sql
    End Sub
    [/code]

    [code]
    Sub DAO_Insert()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    'error handler
    On Error GoTo erh

    'open database/recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl2", dbOpenSnapshot)

    'sample data to add
    Dim Lname As String
    Dim Addr As String
    Lname = "flintstone"
    Addr = "bedrock"

    '***add new record to recordset***
    rs.AddNew
    'put string values into table fields
    rs!LastName = Lname
    rs!Address = Addr
    'update/save recordset
    rs.Update

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    erh:
    MsgBox Err.Description, vbCritical, Err.Number
    Resume Next
    End Sub
    [/code]


  • rcloughrclough Member Posts: 3
    [b][red]This message was edited by rclough at 2006-6-23 18:28:6[/red][/b][hr]
    : [b][red]This message was edited by dokken2 at 2006-6-23 5:4:21[/red][/b][hr]
    : : Hi, I am new with the VBA in Access and becoming very confused. I have a database I am working on for a business. With this I have a form that they can go into and create a bid for their customer. This form has about 25 - 20 listboxes that are set up for multiple selection. I also have a button so when they have the choices they want it will save to a table. This is where my problem is. I've worked a little in VB but not Access VBA. How can I get the choices from the form to save to the table? In my table I have the customer number and a field for each listbox. Also would I do this on the Save_Click event or at the beginnning? The final out come will be a form that they can print out to give to the customer, it will contain the options chosen, customer information and total.
    : :
    : : Thanks for the advice.
    : : rclough
    : :
    :
    :
    : there's several ways to do this with DAO, ADO, SQL,
    : here's the two most common ones-
    : [assume table TBL2 has text fields: LastName, Address ]
    :
    : you would need to run this code after the user has selected all their values, give them a SAVE or UPDATE button
    :
    : [code]
    : Sub Sql_Insert()
    : Dim Sql As String
    :
    : 'sample data to insert
    : 'need to enclose strings within a double or single quote
    : Dim Lname As String
    : Dim Addr As String
    : Lname = Chr(34) & "flintstone" & Chr(34)
    : Addr = Chr(34) & "bedrock" & Chr(34)
    :
    : 'sql to insert into table TBL2
    : Sql = "INSERT INTO tbl2 ( LastName, Address )" & _
    : "SELECT " & Lname & " AS Expr1, " & Addr & " AS Expr2;"
    : '***run sql***
    : DoCmd.RunSQL Sql
    : End Sub
    : [/code]
    :
    : [code]
    : Sub DAO_Insert()
    : Dim db As DAO.Database
    : Dim rs As DAO.Recordset
    :
    : 'error handler
    : On Error GoTo erh
    :
    : 'open database/recordset
    : Set db = CurrentDb
    : Set rs = db.OpenRecordset("tbl2", dbOpenSnapshot)
    :
    : 'sample data to add
    : Dim Lname As String
    : Dim Addr As String
    : Lname = "flintstone"
    : Addr = "bedrock"
    :
    : '***add new record to recordset***
    : rs.AddNew
    : 'put string values into table fields
    : rs!LastName = Lname
    : rs!Address = Addr
    : 'update/save recordset
    : rs.Update
    :
    : rs.Close
    : db.Close
    : Set rs = Nothing
    : Set db = Nothing
    : Exit Sub
    :
    : erh:
    : MsgBox Err.Description, vbCritical, Err.Number
    : Resume Next
    : End Sub
    : [/code]
    :
    :
    : Thanks dokken2, this will help me out a bunch. I've bought a couple of books and I'm waiting for another to come from eBay but I don't have time to go through the way I should be. The person I'm doing this for has me on a deadline and this section has just really thrown me.

    Another question.....for the listboxes should I create a is selected code to have it write the selections to the same table? Also do I do this for each individual listbox and with each option in the listbox?

    Not to be a pain but.....I could also use some suggestions on multiple users with the database. This will be set up for 3 people to use, 2 from an office and 1 from a remote location (his home). I have an ftp that I thought of putting it on for them to access it. They also have a server in the office. Which do you think would be best and should I use code for an ADO Connection?


    Thanks again for the help....I really appreciate it.




  • rcloughrclough Member Posts: 3
    : [b][red]This message was edited by rclough at 2006-6-23 18:28:6[/red][/b][hr]
    : : [b][red]This message was edited by dokken2 at 2006-6-23 5:4:21[/red][/b][hr]
    : : : Hi, I am new with the VBA in Access and becoming very confused. I have a database I am working on for a business. With this I have a form that they can go into and create a bid for their customer. This form has about 25 - 20 listboxes that are set up for multiple selection. I also have a button so when they have the choices they want it will save to a table. This is where my problem is. I've worked a little in VB but not Access VBA. How can I get the choices from the form to save to the table? In my table I have the customer number and a field for each listbox. Also would I do this on the Save_Click event or at the beginnning? The final out come will be a form that they can print out to give to the customer, it will contain the options chosen, customer information and total.
    : : :
    : : : Thanks for the advice.
    : : : rclough
    : : :
    : :
    : :
    : : there's several ways to do this with DAO, ADO, SQL,
    : : here's the two most common ones-
    : : [assume table TBL2 has text fields: LastName, Address ]
    : :
    : : you would need to run this code after the user has selected all their values, give them a SAVE or UPDATE button
    : :
    : : [code]
    : : Sub Sql_Insert()
    : : Dim Sql As String
    : :
    : : 'sample data to insert
    : : 'need to enclose strings within a double or single quote
    : : Dim Lname As String
    : : Dim Addr As String
    : : Lname = Chr(34) & "flintstone" & Chr(34)
    : : Addr = Chr(34) & "bedrock" & Chr(34)
    : :
    : : 'sql to insert into table TBL2
    : : Sql = "INSERT INTO tbl2 ( LastName, Address )" & _
    : : "SELECT " & Lname & " AS Expr1, " & Addr & " AS Expr2;"
    : : '***run sql***
    : : DoCmd.RunSQL Sql
    : : End Sub
    : : [/code]
    : :
    : : [code]
    : : Sub DAO_Insert()
    : : Dim db As DAO.Database
    : : Dim rs As DAO.Recordset
    : :
    : : 'error handler
    : : On Error GoTo erh
    : :
    : : 'open database/recordset
    : : Set db = CurrentDb
    : : Set rs = db.OpenRecordset("tbl2", dbOpenSnapshot)
    : :
    : : 'sample data to add
    : : Dim Lname As String
    : : Dim Addr As String
    : : Lname = "flintstone"
    : : Addr = "bedrock"
    : :
    : : '***add new record to recordset***
    : : rs.AddNew
    : : 'put string values into table fields
    : : rs!LastName = Lname
    : : rs!Address = Addr
    : : 'update/save recordset
    : : rs.Update
    : :
    : : rs.Close
    : : db.Close
    : : Set rs = Nothing
    : : Set db = Nothing
    : : Exit Sub
    : :
    : : erh:
    : : MsgBox Err.Description, vbCritical, Err.Number
    : : Resume Next
    : : End Sub
    : : [/code]
    : :
    : :
    : : Thanks dokken2, this will help me out a bunch. I've bought a couple of books and I'm waiting for another to come from eBay but I don't have time to go through the way I should be. The person I'm doing this for has me on a deadline and this section has just really thrown me.
    :
    : Another question.....for the listboxes should I create a is selected code to have it write the selections to the same table? Also do I do this for each individual listbox and with each option in the listbox?
    :
    : Not to be a pain but.....I could also use some suggestions on multiple users with the database. This will be set up for 3 people to use, 2 from an office and 1 from a remote location (his home). I have an ftp that I thought of putting it on for them to access it. They also have a server in the office. Which do you think would be best and should I use code for an ADO Connection?
    :
    :
    : Thanks again for the help....I really appreciate it.
    :
    :
    :
    :
    : Hi, I used the code for Sub DAO_Insert() at the save button click event and I get an error saying End Sub needed. I really feel dumb now but.....How can I work that with the Private Click event and the Sub event? Here's what the code looks like:

    Private Sub Save_Bid_Click()

    Sub DAO_Insert()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    'error handler
    On Error GoTo erh

    I know it's formatted wrong I'm just not sure how to fix it.

  • dokken2dokken2 Member Posts: 532
    : Another question.....for the listboxes should I create a is selected code to have it write the selections to the same table? Also do I do this for each individual listbox and with each option in the listbox?
    :
    : Not to be a pain but.....I could also use some suggestions on multiple users with the database. This will be set up for 3 people to use, 2 from an office and 1 from a remote location (his home). I have an ftp that I thought of putting it on for them to access it. They also have a server in the office. Which do you think would be best and should I use code for an ADO Connection?
    :
    :
    : Thanks again for the help....I really appreciate it.
    :
    :
    :
    :
    : Hi, I used the code for Sub DAO_Insert() at the save button click event and I get an error saying End Sub needed. I really feel dumb now but.....How can I work that with the Private Click event and the Sub event? Here's what the code looks like:

    Private Sub Save_Bid_Click()

    Sub DAO_Insert()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    'error handler
    On Error GoTo erh

    I know it's formatted wrong I'm just not sure how to fix it.



    The "sub DAO_Insert()" procedure - you are placing it inside the button click event which is wrong.

    You just need to call the procedure, like this-

    [code]
    Private Sub Save_Bid_Click()
    'call the procedure
    DAO_Insert
    End Sub


    Sub DAO_Insert()
    '... more code here
    End Sub
    [/code]


    For the listboxes, I would not update after every single listbox value is selected. More efficient to wait until they are all set then update the table all at once with a Save button.

    I've set up multiple users over the LAN and remotely on a WAN. Some home user log into the network with VPN. Could also use Active Server pages. I haven't set up any web or internet enabled applications so I don't know the details.
Sign In or Register to comment.