How to save to a specific record?

[George]

tblSavedSettings
field1 SettingID (Autonumber)
field2 ControlName (text)
field3 Setting (Numeric Long Int)

cboSection, let's say, has a value of 10

tblSavedSettings field 2 has a value of "cboSection" in one record (could be
any record)

1) How do I read the "10" in the combo box and put it into the row of
tblSavedSettings that has a value of "cboSection" in field2 and then update
the same record with "10" in field3?

2) On Load - how do I read it and put the "10" into frmMain.cboSection ?

 

[BruceM]

I have been reading the question for a while, and am not much clearer than I
was when I started. How did the 10 get into cboSection? Is cboSection a
bound control? If so, to what field? If not, what does it do? When would
you have the value update in Field3? Why the Load event for assigning a
value? I expect the Current event would be the place to determine a field
value, but again your intention is unclear.
It may help if you describe in general terms the database's function and
what you would have happen.

 

[OssieMac]

Hi George,

Try the following. It is untested so feel free to get back to me if you have
a problem.

When in the VBA editor, (if not already done), you will need to select
Tools -> References and then scroll down to Microsoft DAO 3.6 Object Library.
Check the box (don't just select the line) and click OK.

'Code to Save ComboBox value to table

Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then
.Edit 'Places record in edit mode

.Fields("Setting") = Me.cboSection.value

'More fields can be placed here as per previous line if required

.Update 'Forces record to update (Otherwise it does not update)

End If

End With

rsCurrent.Close
Set rsCurrent = Nothing



'*************************************************


'Code to Copy values from table to ComboBox
Dim rsCurrent As DAO.Recordset
Dim strCtrlName as String

strCtrlName = "cboSection"

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT * FROM [tblSavedSettings]")

With rsCurrent

'Note concatenation of single quotes around string variable
.FindFirst "ControlName = " & "'" & cboSection & "'"

If Not .EOF Then 'If EOF then not found
Me.cboSection = .Fields("Setting")

'Can retrieve more fields here as per previous line
End If

End With

rsCurrent.Close
Set rsCurrent = Nothing

End Sub

 

[John Spencer]

Well, I would probably be saving the form name also.

I would probably run the code to store the value in the table in the form's
after update event, although you could use the combobox's change event.

Dim StrSQL as String
StrSQL = "UPDATE tblSavedSettings SET Setting =" & Me.ComboboxName & _
" WHERE ControlName = 'ComboboxName' AND FormName = '" & Me.Name & "'"
CurrentDb().Execute StrSQL, dbFailOnError

One problem there is whether or not the setting already exists? If you are
sure it does then the above is enough. Otherwise you might want to use

IF DCount("*","tblSavedSettings","ControlName = 'ComboboxName' AND FormName =
'" & Me.Name & "'") = 0 Then
StrSQL = "INSERT INTO tblSavedSettings (FormName, ControlName, Setting)" &
" Values('" & Me.Name & ", 'ComboboxName', " & Me.ComboboxName & ")"
CurrentDb().Execute StrSQL, dbFailOnError
Else
StrSQL = "UPDATE tblSavedSettings SET Setting =" & Me.ComboboxName & _
" WHERE ControlName = 'ComboboxName' AND FormName = '" & Me.Name & "'"
CurrentDb().Execute StrSQL, dbFailOnError
END IF

You should be able to use the form's load event to get the value and assign it
to the combobox.

Me.ComboboxName = DLookup("Setting","tblSavedSettings","ControlName =
'ComboboxName' AND FormName = '" & Me.Name & "'")

By the way, if you are going to doing this for multiple controls you might
want to consider setting the field type of the setting field to text and then
converting that saved text as appropriate depending on the control.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County