Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: saving data to a table using unbound form

    I have a main form for users to select their model, serial number etc and then add any inspection comments they might have. They then can click on a button created to add a new nonconformance for the parts. Everything on that form seems to be working and writing to the table and the new nonconformance button is opening the next form for them.

    The problem I have on the second form is they are all unbound because of the combo boxes. I need for them to select something from the first and then the only thing showing in the second is what is available for the items selected in the last box. All of that is working fine as well.

    Where I get into trouble understanding how to get this data to write to the tblnonconformance table. I also need for the primary key number to auto populate to the next availalbe number.

    I can make everything write to a table perfectly when I use the table directly but the combo boxes will not work when they are bound to a table or query....
    I am new to this part of access and have been working on trying to use samples out there for a couple weeks now. If anyone has any suggestions to guide me in the right direction, I would greatly appreciate it.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by ablack View Post
    I can make everything write to a table perfectly when I use the table directly but the combo boxes will not work when they are bound to a table or query....
    I'm not clear on what you have going on, but typically a combo would get its selections from one table (a "lookup" table) but be bound to a field in another table (an "activity" table). In other words, the row source and control source can be different tables.
    Paul

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    I do have several lookup tables for the different combo boxes on the form. This is somthing I am trying to work with as the person who actually created it is no longer with the company.
    I don't see where those lookup tables are linked directly to the nonconformance table because they are also being used to write to the machinedata table on the first form.

    I have the forms created and if I set the combo box to be bound to the table, I can't get the combo box to work correctly. They are choosing an item in the first one and the query will run again to only allow items related to the item chosen in the first box to appear in the second. all of that is working fine. I just can't get the save button to write the data on the form to the table. I've tried several different things and am not familiar enough with vb or sql to know how to just write the code for this button. I would be glad to send you any screens or information you would want to see. thanks

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I guess I still don't understand, but you can add data a few ways. Here's one:

    Code:
      Dim db            As DAO.Database
      Dim rst           As DAO.Recordset
      Set db = CurrentDb()
      Set rst = db.OpenRecordset("SELECT * FROM TableName WHERE 1 = 0", dbOpenDynaset, dbSeeChanges)
    
      With rst
        .AddNew
        !FieldName = Me.TextboxName
        .Update
        .Close
      End With
    
      Set db = Nothing
      Set rst = Nothing
    Paul

  5. #5
    Join Date
    Nov 2009
    Posts
    6
    Thanks I will give this a try. I am very new to the vb side of things.
    basically I have two forms. one is writing to the table just fine and the other is not. THe first form appears to be using a query as the base and that is writing back to the table. The second form is one I've had to create new. they click on a new nonconformance button on the first form and the second opens. It is a series of combo boxes where depending on what they choose in the first is what they will see in the second and so on. Each combo box is looking at a different lookup table. I didn't design it that way but the prior person did. I am trying to work and make changes for the end user with what he had. Because of all the different lookup tables, I am trying to make all of the selections they pick write back to just one table called noconformance. It seems like something very simple but is very difficult for me because of the lack of code knowledge...

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Again, generally speaking I would expect combo boxes that were bound to the non conformance table (control source) but got their selections from a lookup table (row source). Can you post the db?
    Paul

  7. #7
    Join Date
    Nov 2009
    Posts
    6
    here is the database. I have also been working with some more code and have the button to where it is not erroring out but is also not putting the data on the table.. the button I am working with is on the nonconformance form and is called save machine comments here is the code for that button
    Code:
    Private Sub Save_Machine_comments_Click()
     Dim dbs As Database
     Set dbs = OpenDatabase("testonlyfinalinspectionwith sub.mdb")
     dbs.Execute "insert into tblnonconformance" _
      & "(intprimarykey , intinspectionid , strassembly , strcomponent) values " _
     & "(100 , 57 , 'Cab, Upper' , 'Other');"
     dbs.Close
     
     ' & "(nonconformanceform.strproduct.text , nonconformanceform.xassembly.text);"
    
    
    End Sub
    where the values are typed in, I've also tried using my form names

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you mean to attach the db?
    Paul

  9. #9
    Join Date
    Nov 2009
    Posts
    6
    I did but it told me the size was bigger than what was allowed. I'll have to try and see if I can break it down.
    I was able to get this working by using the following code. now I have to add each and every field to the code. not sure if there is a better or a more simple way
    Code:
      Private Sub Save_Machine_comments_Click()
     Dim A As String
     Dim C As String
     Dim P As String
     P = xproduct
     A = xAssembly
     C = xComponent
     Dim dbs As Database
     Set dbs = OpenDatabase("C:\Documents and Settings\ajblack\Desktop\testonlyfinalinspectionwithsub.mdb")
     DoCmd****nSQL "insert into tblnonconformance" _
      & "(strproduct , strassembly , strcomponent) values('" & P & "','" & A & "','" & C & "');"
    
     dbs.Close
     DoCmd.Requery
     
     
     
    
    
    End Sub

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If that works and the other didn't, one difference I see is the intprimarykey field is not in the second. If that's an autonumber field, you don't include it in the query.

    Make sure you compact/repair before zipping. That will get most db's down to an acceptable size.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •