Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    4

    Unanswered: access data base

    I have a form with a combo box (combo48) which gets values from a table (aircraft).This table has 3 columns (reg,type,end)
    Another table (record) records values
    I also have 2 text boxes (type&end)
    When I select a row in the combo box, the values in the 2 columns(type & end)are shown on the form, but not recorded in the record table.The 1st column (reg) is.
    How do I save the values from column 2 & 3 to the table (record)

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    There are too many questions you need to answer before your question can be addressed.

    1 - What is your form's RecordSource?
    2 - What fields are in the 'record' table?
    3 - How is the ComboBox getting data from 'aircraft', is it from the Row Source or some other source (such as Control Source)?
    4 - What is the ComboBox's Row Source Type?

    I'm not playing dumb. These are basic but serious issues. Perhaps by just reading my questions you will understand a lot of what you should do to get where you want.

    Perhaps, as a starter, you should utilize the Help file and read up on using ComboBoxes and using VBA.

    SL

  3. #3
    Join Date
    Jun 2010
    Posts
    4
    1- control source: reg
    2-fields: ID,reg,type,end
    3- row source
    4- row source type: table/query
    I have tried the help file without luck. I am missing something

    F65

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    create a event on the combo box after update

    me.ref = me.combo48.Column([the column number in the combo48])

    if you don't want to show those value in the combo box
    set the Column count to the Number of columns
    then use the Column widths to hide the values
    0cm;2.45cm;0cm

    column 1 and 3 are hidden or 0 or 2 hidden this is off top of head

    by the way "combo48" is not a good name to give a combo box
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jun 2010
    Posts
    4
    Thanks
    I have no problem with what shows in the text boxes on the form, but it doesn't save to the table 'record'
    What should be put in ref (Me.ref)

    F65

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Thumbs up

    Quote Originally Posted by fifteen65 View Post
    1- control source: reg
    2-fields: ID,reg,type,end
    3- row source
    4- row source type: table/query
    I have tried the help file without luck. I am missing something

    F65
    The first item in your answer, 'control source: reg' gives me pause. What I asked you was what the form's Record Source was. If you're answering me that the Combo48's sontrol source was (not my question), I have two comments:

    1 - Obviously, the form's record source is the table 'aircraft,' or you couldn't have ANY control source for the combo box.

    2 - the fact that you have a control source for Combo48 is a problem. This works when you're saving to the same table. However, you're getting source data from table 1 (aircraft) and attempting to save to table 2 (record). This doesn't work if you have a control source AT ALL for the combo.

    The first thing you have to do is to remove the control source for Combo48. In other words, leave it blank. The second thing you need to do is to save the record to the 'record' table with VBA.

    By the way, don't call your table 'record.' That is a Reserved Word, which means that the term 'record' has a special meaning to Access. Using reserved words as names for objects or variables screws up Access's internals. Change the name to, say, "tblRecord".

    Good luck,

    SL

  7. #7
    Join Date
    Jun 2010
    Posts
    4
    Thanks for that. have done what you said, but having trouble with the correct command for saving the record with VBA
    F65

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Now I can get down to finally answering your question.

    I don't know how you coded Combo48, so I'll have to start from scratch. If your entries don't match mine, change them. You may want to make a copy of your existing form "just in case" before you start.

    Make the following observations/changes:

    1 - Make sure that the form's Record source is the table 'aircraft.'
    2 - The combo's Row Source Type should be "Table/Query" in the Property Sheet.
    3 - The combo's Row Source should be
    Code:
    SELECT aircraft.reg FROM aircraft
    4 - The combo's After Update event in VBA should be changed to
    Code:
    Private Sub Combo48_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[reg] = '" & Me![Combo48] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    By the way, all of the above is the same that Access would do if you had wiped out the existing combo and started over again making certain entries in the combo box wizard. I simply saved you the step.

    You may have already done the next two steps partially, since you say the 'reg' field is already being saved correctly. If that's the case, just make the changes necessary.

    5 - Now you need a new command button. Call it, say, "cmdSaveRec". As soon as the wizard starts, cancel it.

    6 - Create an On Click VBA event and type (or copy) the following code:

    Code:
    Private Sub cmdSaveRec_Click()
    
        Dim rstRec As DAO.Recordset
        
        Set rstRec = CurrentDb.OpenRecordset("tblRecord", dbOpenTable)
        With rstRec
            .AddNew
            !reg = Me.Combo48.value
            !type = Me.type
            !end = Me.end
            .Update
            .Close
        End With
        
    End Sub
    I'm suggesting DAO because that's the way I do it: it's simpler. If you use ADO, you know how to modify the code. If not, you can ask that question on the forum; I admit I don't know ADO.

    This just might be all you need.

    Good luck,
    SL
    Last edited by Sam Landy; 07-04-10 at 13:35.

Posting Permissions

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