Noob Insert/update/delete queries

[tenderfoot]

Hi guys,

I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?

Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?

1. I need to create a form that inserts people's details into a table

1. When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.

Thanks in advance!

 

[ajetrumpet]

Here are some ideas...

I need to create a form that inserts people's details into a table

For this, simply set the recordsource of your form to the "people's" table. You can create an autoform (form based on the table itself) from the database window, or form wizard.

When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.

First, you will have to write the query, and then use the DoCmd.OpenForm command in VB to open the form to the specified record that was queried.

You may not even need a query either. If all the surnames you are going to be entering into this table are going to be unique, then all you need is the OpenForm command with a condition. No query will be needed.

 

[tenderfoot]

Thanks for your help Adam!

I am alittle unsure about how to set the record source. Is this something I can do in design view? I have tried right clicking on the form in design view and looking in "properties" I cannot see Record source.

I created a form using auto form for the "People" table like you advised and this pulls out all the people in the table through the form, you can skip through the records using the arrow button at the bottom.

How can I set the recordsource and set the form to be blank so it does not pull out any records, it just presents users with an empty form to insert data though into the table?

 

[ajetrumpet]

How can I set the recordsource and set the form to be blank so it does not pull out any records, it just presents users with an empty form to insert data though into the table?

Both of the property lines you are going to want can be found in the DATA tab page in the properties window. The recordsource line is at the top somewhere, and the "data entry" line is somewhere on the tab as well. To get no records, and only specify the form for data entry purposes, set the "data entry" property to YES.

If you made an autoform from the "people" table, then there is no need to change the recordsource, as it has already been set to that table.

 

[tenderfoot]

Sorry to be a pain, but the data tab is greyed out with no options available to me. Is something not enabled somewhere?

 

[ajetrumpet]

what version of access are you using?

 

[ajetrumpet]

Ha! nevermind, I see it in the first post!!

Paul said something awhile ago about code running in the background when the application starts. I wonder if it is doing it to you too, although I can't remember what version that was...

 

[tenderfoot]

I think I now know why the data tab was greyed out. At the top of the properties box the dropdown had not been selected. I clicked this dropdown and selected form and the data tab came alive...I have seen what you mean about the record source and I have now set the data entry to "Yes". I have tested this now and I have got a record inserted. Thanks!

Will try and give the update a record a go!


Using MS Access 2002 btw if the number next to Access 2002 part in the "about" section is a version number then it is version 10.26.

 

[tenderfoot]

Success! The update is the same almost...I chose the parameter query I created as the record source for the update form. Without knowing about recordsource I would not have known how to do this.

So, the record is pulled according to the person's surname and the user enters this through a dialog box.

Thanks again for all your help Adam!

 

[ajetrumpet]

Awesome! Way to go! You're smarter than you think, you did most of the work here!

Good luck.