SQL and forms

[soloman02]

If I had my way, I'd be using SQL Server and PHP to create web based forms but my professor doesn't want that he wants us to use Access for the database and to create the UI, but we have to use SQL for all the queries.
My database is a simple one that holds customer info and info about their PC's that need repair and what repairs were performed. I have my tables done and the relationships done. I believe they are in 3NF form, if not they are very close, but that isn't where I am stuck.

I have a form to add data (Customer Info) to the Customer table. The user can enter the data into the fields of my form and then press a button to save the data. That button has an event with SQL code. The problem is if I use the INSERT INTO command what do I use for the values? I want the data in the fields to be the values but I don't know how to reference them.

I don't know VBA at all, can I just put the SQL code in the VBA code builder like below or do I have to do something special with it?

Also C_ID is my PK and is set to be an autonumber. I think my professor wants that done in SQL but getting the forms to work is a bigger priority.

INSERT INTO Customer ([C_ID], [C_FNAME], [C_LNAME], [C_ADDRESS], [C_TOWN], [C_STATE], [C_PHONE], [C_EMAIL]) VALUES (What do I use to reference the form fields?);

I am using Access 2013 at work but I have provided both the .accdb and .mdb files.

Any assistance is much appreciated.

 

[MarkK]

Hey there, welcome to the forum.

In an Access Form you can set the RecordSource property to a SQL SELECT statement and simply bind individual controls to fields in the implicitly created Recordset by setting the ControlSource properties of the aforementioned controls to the names of Recordset.Field objects. If you take this approach the form is said to be "bound," and updates, inserts and deletes can be handled by the Form object itself without further code or SQL. Understanding this approach in Access is useful since it allows very rapid development and prototyping and is probably the fastest way available to go from nothing, to a working user interface on a relational database system, so you can start collecting data. Then, as time goes by, you can add as much or as little error checking and user interface frills as you want.

To create an unbound form and write a series of SELECT, INSERT, UPDATE, and DELETE statements, and manually execute data access like that defeats a lot of the simplicity that is one of Access's main advantages, and is probably one of the reasons your professor wants you do it that way.

Cheers,

 

[soloman02]

Hey there, welcome to the forum.

In an Access Form you can set the RecordSource property to a SQL SELECT statement and simply bind individual controls to fields in the implicitly created Recordset by setting the ControlSource properties of the aforementioned controls to the names of Recordset.Field objects. If you take this approach the form is said to be "bound," and updates, inserts and deletes can be handled by the Form object itself without further code or SQL. Understanding this approach in Access is useful since it allows very rapid development and prototyping and is probably the fastest way available to go from nothing, to a working user interface on a relational database system, so you can start collecting data. Then, as time goes by, you can add as much or as little error checking and user interface frills as you want.

To create an unbound form and write a series of SELECT, INSERT, UPDATE, and DELETE statements, and manually execute data access like that defeats a lot of the simplicity that is one of Access's main advantages, and is probably one of the reasons your professor wants you do it that way.

Cheers,

How do I set the RecordSource property to a select statement? Do I have to open the query builder? The form is currently set to reference the table and that is the only option unless I open the query builder.

How do I bind individual controls to fields in the implicitly created Recordset?

How do I find the names of Recordset.Field objects?

I know one thing for sure. I'm not gonna be a DBA. This is making me want to :banghead:

 

[spikepl]

1. WHat prevents you from spending 1 hour on some Access Forms and an Access Queries tutorial?

2. What prevents you from downloading a functioning db from the samplrs section here or from Microsoft Access Templates and having a peek at what makes it tick?

3. If you are as far as guessing that you can open the query builder to produce a query... what prevents you from actually trying that? Access doesn't zap you or anything. Make a copy of your db, and then go crazy - press the buttons and see what happens.

Get some basics under the belt, because doing this via a forum is not very effective.

 

[MarkK]

Yeah, as Spike suggests, punch 'Form.RecordSource' into Access help. Then try: 'Textbox.ControlSource', and so on, and they have better descriptions than I could write for you.

The name of the fields in a recordset are defined by the SQL you wrote. But you run SQL Server from PHP, no? You must open a recordset in a page to get data from the server using SQL SELECT statements. No?

hth