asked on

Appending new record to Access Table from Form Input

I have created a two page form in Access to acquire user input on patient surgical infection outcomes. it has about 30 fields to be entered yes/no or with a comment.

I have a Table with a field to accept the data from every field on the form,

Form Field= ChkVomit Y/N format Table Field = Vomit Y/N format

What would the syntax be to Insert Into the TABLE from the FORM fields?

Thanks

Glen

Jim Horn

If your form is bound to that table (i.e. the form's record source property is your table name), then you can map the columns to each textbox using the Row Source property.

Pull that off and you won't need to create a separate query to handle the INSERT.

GPSPOW

ASKER

Form is not bound to the Destination table.

Glen

Kelvin Sparks

UPDATE yrtable
SET fieldname = Me!chkVomit
WHERE tableID = me!fieldwithtableID

Kelvin

Jeffrey Coachman

<No pints wanted>

In other words, you can use the form wizard to create your form.
Select your table as the forms source...
...Then follow the rest of the wizards steps

JeffCoachman

GPSPOW

ASKER

So it would be:

Update tblSSIInput
Set SIP = me.ChkSIP
Set SIS = me.ChkSIS
Set PURULENT = me.ChkPur
Set VOMITING = me.ChkVomit
Set COMMENT_ORGAN = me.txtOrg

Do I need the "WHERE" clause?
All the data is coming from the FORM fields.

Thanks

Glen

Kelvin Sparks

Update tblSSIInput
Set SIP = me.ChkSIP,
SIS = me.ChkSIS,
PURULENT = me.ChkPur,
VOMITING = me.ChkVomit,
COMMENT_ORGAN = me.txtOrg
WHERE ....

Yes you need the where clause or every record in table tblSSIInput will have these fields updates to these values.

Kelvin

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

Thanks

After reading more about this, I agree with you. I will reconfigure my form and approach.

Thanks

Jeffrey Coachman

To be clear, ...I was not trying to take a way from kelvinsparks' post.
kelvinsparksis perfectly valid if the form needed to be unbound.

There are reasons why an Access form should be unbound, ...but looking at your question, all you were doing was adding records (and probably viewing editing and deleting)

All of this functionality is included in a bound form.

Again, .if you go unbound, you are forced to write code for every user action...

JeffCoachman

Kelvin Sparks

I'd agree with Jeff. The bound form solution is the easiest and most practical solution in most cases. I'd assumed that you'd considered and discarded that solution.

Kelvin