updating a record

[markjkubicki]

I have a table with a set of fields that is identical to the set of fields in a second table.
The first table has and will only have one record. (the user will have the option of changing any of the entries in that record)


The second table has many records (the user cannot modify these records, as they serve as a sort of template for the record in the first)

Q:
Is there a way to updates all of the fields in first table with data from a record in the second table where a criteria has been met

something like this:
update all of the fields in the first table with the data from the record in the second table where some field in that record = some data


many thanks in advance,
mark

[ajetrumpet]

yes there is. use an UPDATE query. or if it is too complicated, use recordsets in code.

[markjkubicki]

this newbie is not familiar with either (I tried the help tables and other web forums, but just got confused). would you be able to direct me to an example (that usually helps me alot)

remember: the catch is that I want to update all of the fields in a record to the values of the fields in a record in an 'update to' table (both records have the exact same fields)

[markjkubicki]

would an SQL statement like this make any sense:

UPDATE FirstTable
SET * = SecondTable.*
WHERE SecondTable.PrintOption = me.cboPrintOption;

[weekend00]

would an SQL statement like this make any sense:

UPDATE FirstTable
SET * = SecondTable.*
WHERE SecondTable.PrintOption = me.cboPrintOption;

I don't think this will work. you need to list all fields in your query. Or you can delete and then insert, * can use in insert statement.

[markjkubicki]

I currently have delete and insert* statements

The record source for the current form is the record that is being deleted, and subsequently being 'replaced' by the inserted record.

however, when I run a me.refresh command to get the form to display the new record, I get a form of null values (which makes sense, because it's source is still pointing to a record that has been deleted). I have not been able to get it to poiont to the (new) inserted record, and to display that data.

(the record source table will have only and never more than 1 record.)

[weekend00]

did you try me.requery?

[markjkubicki]

tired that..


heres my code (behind the AfterUpdate event):

Dim sSQL As String
DoCmd.SetWarnings False
sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
DoCmd.RunSQL sSQL
sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
Me.Requery

[weekend00]

So, you mean there does be one record in the table after delete and insert, and you still get nulls in the form after requery, right?

[markjkubicki]

that's correct

[weekend00]

this looks strange to me.

[markjkubicki]

me too

[weekend00]

try requery then refresh.

[markjkubicki]

still nothing

[ssanfu]

I've modified your code. Execute the following code. What are the recults??

Dim sSQL As String
'==============================
'for testing
'==============================
Dim R as DAO.Recordset
Dim RC as Long 'record count

MsgBox "Option (criteria) selected is " & Me.frmPresetOption

sSQL = " SELECT *" _
sSQL = sSQL & " FROM tblFixtureSchedulePrintOptions"
sSQL = sSQL & " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
' open recordset
Set R = Cuttentdb.OpenRecordset(sSQL)
RC = R.Recordcount
R.Close
Set R = Nothing
MsgBox "Number of records = " & RC
'==============================
'end testing
'==============================


'clear table
sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
CurrentDB.Execute sSQL, dbfailonerror
'append new record
sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
CurrentDB.Execute sSQL, dbfailonerror
'requery current form
Me.Requery



Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)