Change DefaultValue in a Table using a combobox ion a form ...how?

[Guest]

How can I change the Default Value in a Table using a combo box? I know I can
change the default Value in the table by open the Table in design view and
change it as needed (the default Value will always be numerical 1, 2, 3...
etc.). I don't like the idea of users messing with the table directly and I
prefer have combo box/list box list the available values and save it to the
Table as Default Value.

Thank you,
Silvio

 

[Allen Browne]

It is possible to programmatically assign a value to:
Currentdb.TableDefs("MyTable").Fields("MyField").DefaultValue

However, I think it would be much better to create a table to hold the
default values, and assign them to the controls on your form instead of to
the fields of the table. You can create a bound form where the user can set
these values, and then assign them to the controls in the Load event of your
form.

The table would have fields:
TheForm Text name of the form this applies to.
TheControl Text name of the control the value should be assigned
to.
TheType data type (so you can validate it is
acceptable.)
TheValue Text the actual value to assign.

TheValue is stored as text, which is appropriate as Default Value is a
String value.

 

[Guest]

Thank you much. This is what I have done and it work just fine:
CurrentDb.TableDefs("tbladd").Fields("office").DefaultValue = Me.CmboOffice

Now ... How can use the same system to chance the Criteria in a query?
I need the query to retain, even after closing the form, the same value in
criteria as in Default value in table. I guess it should be something like
this but it does not work:
CurrentDb.QueryDefs("qryData").Fields("Office").Properties("Criteria") =
Me.CmboOffice

Can anyone help please?

 

[Allen Browne]

You would need to change the SQL property of the QueryDef:
Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("qryData").SQL = strSql

Rather than parse the WHERE clause from the query, and try to modify it, it
would probably be easier to just create the SQL string on the fly, and then
assign it to the RecordSource of your form or report. If you prefer, you
could just create the WHERE clause, and use it as the Filter of your
form/report, or in the WhereCondition of OpenForm/OpenReport.

 

[Guest]

The reason I am doing all this is because I have 3 offices located in
different city and I want them to access the same database. However, to run
reports, form, etc. I want them to retrieve/filter data for their local
office only; so by setting a criteria in the query (the office ID) for which
the reports etc. are based on, I don’t need to design a different report etc
for each office. Can you be more specific as for write the SQL statement? I
haven’t done databases for years and I am rusty :-(

Thank you

 

[Allen Browne]

Create a query that chooses the right data for one of the offices.
Switch to SQL View (View menu in query design.)
Copy what you see there to the string.