Update table from form

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

Guest

I have a table where the fields are a number of check boxes.
I want to run a report from this table where text is displayed according to
the values in the check boxes. I can do this bit!
However I want the check boxes to be filled depeding on the answers to
various questions in a form. I know how to do the last bit but how do I
update field in the table with the value in the form which is bound to an if
expression of another field?

I would only ever want to update one record in the table at a time.

If possible I am looking for a solution that doesn't involve coding VBA as I
don't have enough experience for this

Robseym
 
What you want to do is bind the controls to a tables, and open the report
based upon the values on the form. For that you really need VBA code, but
relax, the code is really easy, and I'll write it for you. All you need to
do is substitute your field and control names.

Create a command button. Name it something like cmdOpenReport. On the Events
tab of the property sheet, look for the Click event, then press the down
arrow in the box and select: [Event Procedure]. Now click on the Ellipses
button (...) to open the procedure, and you'll see something like:

Private Sub cmdOpenReport_Click()

End Sub

Now type a line inside the procedure like:

Private Sub cmdOpenReport_Click()
DoCmd.OpenReport "Your Report Name",,,,"ID =" & Me.txtID
End Sub

You need to make the following substitutions:

Your Report Name - use your report's name

ID - the name of the ID or Key field in the underlying table that the report
(and your form) is based upon

txtID - the name of the textbox that contains the ID field in your form
(txtID may have the same name as the field, so it's a good idea to change
the name in the form and report to avoid confusion)

That's it! You are starting to program now. In a short time you'll be a
master of VBA and posting regular answers in the newsgroup.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thank you for your very quick response

Slight change of plan I have decided to dispense with the report and instead
produce the final document by means of a mail merge in word running from the
source table. This may sound clumsy but there is a lot of text and word makes
it easier to format for me and a lot of content is already there.

therefore all i need is the bit which changes the values of field in the
table in response to the answers given in other fields. To give an example
lets say I have a paragraph which I call 'para1' - the actual text only
appears in the mail merge

I would have a yes/no field in the table called 'para1'. On the form I have
a text box called 'para1' which becomes checked if another field contains a
certain value (I can do this). All I need is to get the textbox to update the
para1' field in the table so that the table can be used in the mail merge.

Arvin Meyer said:
What you want to do is bind the controls to a tables, and open the report
based upon the values on the form. For that you really need VBA code, but
relax, the code is really easy, and I'll write it for you. All you need to
do is substitute your field and control names.

Create a command button. Name it something like cmdOpenReport. On the Events
tab of the property sheet, look for the Click event, then press the down
arrow in the box and select: [Event Procedure]. Now click on the Ellipses
button (...) to open the procedure, and you'll see something like:

Private Sub cmdOpenReport_Click()

End Sub

Now type a line inside the procedure like:

Private Sub cmdOpenReport_Click()
DoCmd.OpenReport "Your Report Name",,,,"ID =" & Me.txtID
End Sub

You need to make the following substitutions:

Your Report Name - use your report's name

ID - the name of the ID or Key field in the underlying table that the report
(and your form) is based upon

txtID - the name of the textbox that contains the ID field in your form
(txtID may have the same name as the field, so it's a good idea to change
the name in the form and report to avoid confusion)

That's it! You are starting to program now. In a short time you'll be a
master of VBA and posting regular answers in the newsgroup.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Robseym said:
I have a table where the fields are a number of check boxes.
I want to run a report from this table where text is displayed according to
the values in the check boxes. I can do this bit!
However I want the check boxes to be filled depeding on the answers to
various questions in a form. I know how to do the last bit but how do I
update field in the table with the value in the form which is bound to an if
expression of another field?

I would only ever want to update one record in the table at a time.

If possible I am looking for a solution that doesn't involve coding VBA as I
don't have enough experience for this

Robseym
Click to expand...
Click to expand...
 
Use a query with an Immediate If statement:

Expr1: IIf([Field to check] = "X", [Para1],"")

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Robseym said:
Thank you for your very quick response

Slight change of plan I have decided to dispense with the report and instead
produce the final document by means of a mail merge in word running from the
source table. This may sound clumsy but there is a lot of text and word makes
it easier to format for me and a lot of content is already there.

therefore all i need is the bit which changes the values of field in the
table in response to the answers given in other fields. To give an example
lets say I have a paragraph which I call 'para1' - the actual text only
appears in the mail merge

I would have a yes/no field in the table called 'para1'. On the form I have
a text box called 'para1' which becomes checked if another field contains a
certain value (I can do this). All I need is to get the textbox to update the
para1' field in the table so that the table can be used in the mail merge.

Arvin Meyer said:
What you want to do is bind the controls to a tables, and open the report
based upon the values on the form. For that you really need VBA code, but
relax, the code is really easy, and I'll write it for you. All you need to
do is substitute your field and control names.

Create a command button. Name it something like cmdOpenReport. On the Events
tab of the property sheet, look for the Click event, then press the down
arrow in the box and select: [Event Procedure]. Now click on the Ellipses
button (...) to open the procedure, and you'll see something like:

Private Sub cmdOpenReport_Click()

End Sub

Now type a line inside the procedure like:

Private Sub cmdOpenReport_Click()
DoCmd.OpenReport "Your Report Name",,,,"ID =" & Me.txtID
End Sub

You need to make the following substitutions:

Your Report Name - use your report's name

ID - the name of the ID or Key field in the underlying table that the report
(and your form) is based upon

txtID - the name of the textbox that contains the ID field in your form
(txtID may have the same name as the field, so it's a good idea to change
the name in the form and report to avoid confusion)

That's it! You are starting to program now. In a short time you'll be a
master of VBA and posting regular answers in the newsgroup.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Robseym said:
I have a table where the fields are a number of check boxes.
I want to run a report from this table where text is displayed
Click to expand...
according
to
the values in the check boxes. I can do this bit!
However I want the check boxes to be filled depeding on the answers to
various questions in a form. I know how to do the last bit but how do I
update field in the table with the value in the form which is bound to
Click to expand...
an
if
expression of another field?

I would only ever want to update one record in the table at a time.

If possible I am looking for a solution that doesn't involve coding
Click to expand...
VBA as
I
don't have enough experience for this

Robseym
Click to expand...
Click to expand...
Click to expand...
 
Back
Top