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)
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.
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
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
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.
1- control source: reg
3- row source
4- row source type: table/query
I have tried the help file without luck. I am missing something
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".
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
SELECT aircraft.reg FROM aircraft
4 - The combo's After Update event in VBA should be changed to
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
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:
Private Sub cmdSaveRec_Click()
Dim rstRec As DAO.Recordset
Set rstRec = CurrentDb.OpenRecordset("tblRecord", dbOpenTable)
!reg = Me.Combo48.value
!type = Me.type
!end = Me.end
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.