Button to move fields to another table?

[vidus]

Gday. I am fairly new here I guess, I have managed to program some simple buttons on my form but this one I am in over my head and looking for some help.

I need to create a button that moves the following fields of the record in view from table [2009] to a new record in table [Job Board]. The fields moved will be:

[Job] ----> [Job]
[Company] ----> [Customer]
[Description] ----> [Job Name]

Ive had some suggestions from people about dlookup, and other random things that I have no idea how to use. Any guidance would be very very appreciated.

Thanks so much.

 

[MarkK]

All you are doing is changing the status of the object. In this case you don't need to move the object, simply add a status field--in this case 'IsOnJobBoard'--to the table.

tJob
JobID
Job
Company
Description
Year
IsOnJobBoard

And JobBoard is a query, not a table, with SQL as follows...

SELECT * FROM tJob WHERE IsOnJobBoard

With this design all you need on the Job form (or the JobBoard for that matter) to toggle an item's presence on the JobBoard is this...

Private cmdToggleIsOnJobBoard_Click()
[COLOR="Green"]  'simply toggles the value of the field, which can only be true or false[/COLOR]
  Me.IsOnJobBoard = Not Me.IsOnJobBoard
End Sub

Cheers,

 

[vidus]

All you are doing is changing the status of the object. In this case you don't need to move the object, simply add a status field--in this case 'IsOnJobBoard'--to the table.

And JobBoard is a query, not a table, with SQL as follows...

SELECT * FROM tJob WHERE IsOnJobBoard

With this design all you need on the Job form (or the JobBoard for that matter) to toggle an item's presence on the JobBoard is this...

Private cmdToggleIsOnJobBoard_Click()
[COLOR=Green]  'simply toggles the value of the field, which can only be true or false[/COLOR]
  Me.IsOnJobBoard = Not Me.IsOnJobBoard
End Sub

Cheers,

You lost me after JobBoard is a query not a table... whhhaaaa?

I dont want to just toggle them on and off, I just want to move the info in the fields, instead of doing the old copy and past from one table to another. Either way, I is confuse.

 

[MarkK]

A table should contain a type of object, say 'Person'. You don't create a new table for people named Bob and a new table for people named Sue. Their names are data that occupy a field in the table. So a 'Name' is a property of a 'Person'. Now lets say we want of list of people whose name starts with the letter 'S'. Do we create a new table? No. 'Name' is data, a property of a person. This criteria...

WHERE Left([Name], 1) = 'S'

...specifies a subset of people in the People Table. This subset IS the list of people whose name starts with 'S'. We don't create a new table or move records.

In your case you deal with a Job object. A property of that Job is whether it appears on the JobBoard. Do you create a new table? No. IsOnJobBoard is data, a property of a job. This criteria...

WHERE IsOnJobBoard = True

...specifies a subset of jobs that are on the JobBoard. This subset IS the JobBoard. There is no reason to create a new table or to move records.

Restated:
The JobBoard IS a subset of records in the Job table and doesn't require it's own table. Changing a Job's OnJobList property does not require moving the record, it only requires changing the value of a single property of the job.

If you have a user interface element, say a form, that displays 'The JobBoard', give it a record source of ...

"SELECT * FROM tJob WHERE IsOnJobBoard = True"

 

[vidus]

Do you create a new table? No

But thats the thing, once we move a job from [2009] to [Job Board] it contains a completely different set of data. 2009 is quotes, job board is jobs we have won the contract for. So having a seperate table is needed.

I cant start rearranging our database, I just want to create this button for our existing tables.

 

[MarkK]

OK, so you're creating a Job from a Quote.
There are a bunch of ways to do this. Will you add other data to the job when it is created? Maybe you want to open a form with a new job in it and write the Quote data to fields on that form. This might be the simplest way, but maybe not appropriate in your workflow?
You can open a recordset, add a record and copy the data from the quote form into the recordset. This is fairly simple to understand and perhaps a good choice if you don't need your user to interact with the new Job when it is created.
You can also write an insert query. This will execute the most quickly, but is a bit more difficult to write and to understand.

 

[vidus]

You can open a recordset, add a record and copy the data from the quote form into the recordset. This is fairly simple to understand and perhaps a good choice if you don't need your user to interact with the new Job when it is created.

There will be other fields in the new record that we would fill in later so not sure if this would work but it sounds like what we want to do. We are just trying to reduce the time from copying and pasting the fields in my original post to the Job Board.

 

[MarkK]

Code for this would look a lot like...

private sub cmdCreateJobFromQuote()
  dim rst as dao.recordset

[COLOR="Green"]  'create the recordset to which job record will be added[/COLOR]
  set rst = currentdb.openrecordset( _
    "SELECT * FROM tJob")
  with rst
[COLOR="Green"]    'add a record[/COLOR]
    .addnew
[COLOR="Green"]    'copy data from the current quote record to the new job record[/COLOR]
    !Job = me.job
    !Customer = me.company
    ![Job Name] = me.description
    .update
    .close
  end with
end sub

 

[vidus]

Code for this would look a lot like...

private sub cmdCreateJobFromQuote()
  dim rst as dao.recordset

[COLOR=Green]  'create the recordset to which job record will be added[/COLOR]
  set rst = currentdb.openrecordset( _
    "SELECT * FROM tJob")
  with rst
[COLOR=Green]    'add a record[/COLOR]
    .addnew
[COLOR=Green]    'copy data from the current quote record to the new job record[/COLOR]
    !Job = me.job
    !Customer = me.company
    ![Job Name] = me.description
    .update
    .close
  end with
end sub

thanks for that. not getting anything yet but ill keep playing around with it!