Using VBA to add records

[mrh]

Following the assistance given here http://www.access-programmers.co.uk/forums/showthread.php?p=764611 I thought I progress a bit!

I have a form with 3 fields, read from several sources, and some manual text boxes.

These fields are on a form called "Machine Status" and the 3 fields are "Machine", "Time" and "Run Status"

I am trying to code the bit of vba that adds these 3 values to a new record in a table.

The table is called "Machine Status", and the fields are "Machine", "Time" and "Run Status" also.

Please can someone point me in the right direction?


Many thanks


Matthew

 

[KenHigg]

May I assume the obvious option of having the form bound to the table will not work?

 

[mrh]

Afraid not - the user is tweaks and changes the data before it is added to the new table.

Thanks

Matthew

 

[KenHigg]

Can you elaborate? Those are not good reasons not to bind the form as you should be able to tweak the data before a new record is added / saved...

 

[1jet]

based on the information u've given
i'd simply use

dim strSQL as string

strSQL = " UPDATE yourtablename " & _
             " set yourtablename.machine = Forms!MachineStatus!machine " & _
             " and yourtablename.time = Forms!MachineStatus!time " & _
             " and yourtablename.runstatus = Forms!MachineStatus!runstatus ; "
DoCmd.RunSQL strSQL

strSQL = ""

This is assuming you want to UPDATE the table, as you asked in the forum.
If you want to ADD the data, then the SQL query would need to be an INSERT query

 

[mrh]

Can you elaborate? Those are not good reasons not to bind the form as you should be able to tweak the data before a new record is added / saved...

Hi KenHigg,

The form is based on a query, so, as a beginner, I don't think I can bind it to a table?

Hopefully I'm wrong!

Many thanks

Matthew

 

[KenHigg]

Sorry, Kind of a play on words. A common set up is to have a form that displays fields in a table. You can, with certain limits, base a form on a query as well. Based on this can you elaborate? When you add/change data in the form the way you have it now does it change the data in the table the forms query is based on?

 

[mrh]

based on the information u've given
i'd simply use

dim strSQL as string

strSQL = " UPDATE yourtablename " & _
             " set yourtablename.machine = Forms!MachineStatus!machine " & _
             " and yourtablename.time = Forms!MachineStatus!time " & _
             " and yourtablename.runstatus = Forms!MachineStatus!runstatus ; "
DoCmd.RunSQL strSQL

strSQL = ""

This is assuming you want to UPDATE the table, as you asked in the forum.
If you want to ADD the data, then the SQL query would need to be an INSERT query

Thanks 1jet.

I've played around and got..

Dim strSQL As String

strSQL = " INSERT machinestatus " & _
" set machinestatus.machine = Forms!MachineStatus!machine " & _
" and machinestatus.time = Now() " & _
" and machinestatus.runstatus = "Idle" ; "
DoCmd.RunSQL strSQL

strSQL = ""



So to simplify things I'm trying to update the time with the time the current time the record is created, and the run status with "idle".

Have I made the correct changes?

Thanks

Matthew

 

[mrh]

Sorry, Kind of a play on words. A common set up is to have a form that displays fields in a table. You can, with certain limits, base a form on a query as well. Based on this can you elaborate? When you add/change data in the form the way you have it now does it change the data in the table the forms query is based on?

Cheers Ken,

I'll try and explain what I trying to do. I fear it is very simple really, but I'm making hard work of it!

It's a simple record of if a machine is running, and what time it started - so we can calculate how many hours a week the machines are running for.

I have a table [Machines] with a list of machines - a machine number and a machine description. i.e.

1 CNC Lathe 1
2 Milling Machine
3 CNC Lathe 3

I have another table [MachineStatus] in which is stored the machine number, the time (and date), and the status - either running or idle.

The query [currentmachinestatus] looks at the [MachineStatus] and provides a list of each machine and the last entry made, so I end up with something like....

1 CNC Lathe 1 Idle 17:01:40 14/10/08
2 Milling Machine Running 14:34:23 13/10/08
3 CNC Lathe 3 Running 08:01:40 14/10/08


I then displayed this as a continuous form with a button next to each record to change the status.

I was planning (and have tried) to make this button add an entry into the table, then refresh the form to display the updated data.

Does that make more sense?

Thanks for your help,


Matthew