Change fields in database using VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

want to add to a field in my Calculations Table in my Code. I is used to keep track of what test I'm on. I also want to populate fields in my database based on the clicking of a button. I know you can use the control source property, but I don't want anything changed unless the user clicks OK. I appreciate any help in advance

Brad
 
You didn't say whether you were adding the field via the standard interface
or whether you wanted to do it through code ... the simplest way is to open
the table in design view and just add your column.

You didn't say what version of Access you are using:

To update this column, add code like this behind your button click:

A2000 or greater:
CurrentProject.Connection.Execute "UPDATE NameOfYourTable SET
NameOfYourColumn=Whatever WHERE UniqueRecordID=" & Me!UniqueIDOfRecord

If you're using A97, you can issue the same command via the RunSQL action:
DoCmd.RunSQL "UPDATE NameOfYourTable SET NameOfYourColumn=Whatever WHERE
UniqueRecordID=" & Me!UniqueIDOfRecord

Obviously you will have to replace NameOfYourTable, etc with valid
table/field names

Brad said:
want to add to a field in my Calculations Table in my Code. I is used to
Click to expand...
keep track of what test I'm on. I also want to populate fields in my
database based on the clicking of a button. I know you can use the control
source property, but I don't want anything changed unless the user clicks
OK. I appreciate any help in advance
 
I'm using Access 2000. I have a form with textboxes, combo boxes..etc. The user will populate the form using an Access Interface and I have a "OK" button which I would like to be the final verification. If I just set the control source property to a certain field, it populates the table as soon as it loses focus. I would like to populate my fields using the click event of the button..ex. something like Field = cboTest.Value etc...
 
Then you need to look at using unbound forms (i.e. forms that have no
Recordsouce) and explicitly saving the data upon buttonclick. There really
is no way to tell Access to NOT save a bound form. Using unbound forms
removes a lot of the functionality that makes Access the useful tool that it
is, but it can be done.

When saving, open a recordset based on the table(s) where you wish the info
saved:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM tblYourTable WHERE 1=0", currentproject.connection,
adOpenDynamic, adLockPessimistic

rst.AddNew
rst("Field1") = Me.ctlControl1
rst("Field2") = me.cboControl2.column(0)
etc

rst.Update

rst.close
Set rst = nothing

OR use an Insert query:

currentproject.connection.execute "INSERT INTO tblYourTables(Field1, Field2)
VALUES(Value1, VAlue2)


Brad said:
I'm using Access 2000. I have a form with textboxes, combo boxes..etc. The
Click to expand...
user will populate the form using an Access Interface and I have a "OK"
button which I would like to be the final verification. If I just set the
control source property to a certain field, it populates the table as soon
as it loses focus. I would like to populate my fields using the click event
of the button..ex. something like Field = cboTest.Value etc...
 
Back
Top