Changing records by query?

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a form that shows the current record (From table). I have a cmd
button that uses the values from the current record to make a new record
with certain fields that are the same as the original record. This works
OK. Now comes the part that I don't know about.
Both records have a StartDate and an EndDate. When I click the command
button I need the Original record's EndDate to be changed to the StartDate
of the new record. This makes the new record supercede the old record. I
have a textbox on the form that show the original records ID

I thought if I could make a select query for the ID then change it this
might work. I am not sure how to add this query to my cmd button.

What is the best way to code the cmd button.
Maybe there is a better way??
 
What code is behind your cmd button now?
how are you populating the form with the old record?
 
I have a form that shows the current record (From table). I have a cmd
button that uses the values from the current record to make a new record
with certain fields that are the same as the original record. This works
OK. Now comes the part that I don't know about.
Both records have a StartDate and an EndDate. When I click the command
button I need the Original record's EndDate to be changed to the StartDate
of the new record. This makes the new record supercede the old record. I
have a textbox on the form that show the original records ID

I thought if I could make a select query for the ID then change it this
might work. I am not sure how to add this query to my cmd button.

What is the best way to code the cmd button.
Maybe there is a better way??
Click to expand...

You don't need a query to do this. It sounds like the command button
is starting a new record with certain fields retaining previous data.
To do what you want use the form's On Current event and chech for a
new record if it is a new record set the new records StartDate control
to the old records EndDate control. Something like...

Private Sub Form_Current()
If Me.NewRecord Then
Me.txtStartDate = Me.txtEndDate.OldValue
End If
End Sub

- Jim
 
Jim, Thank for the reply.
This works for the new record but what of the old record?. OK...I have the
form loading with the new record showing, with the dates superseding the old
record. The problem is how to update the old record. I need this old
record changed in the command button code so if I decide to "Cancel" the
record updating I can undo and close. This way the original record is not
updated and the new record is not stored.

This is the process I need:
Save values of original record (works)
Open form to new record (works)
Use original record data to fill data in form for new record (works)
Update EndDate of original Record (NOT WORKING)
Close or Cancel (works)
 
The Form loads, goes to new record, and the fields are filled. At this point
I need to be able to click the save button and the original records EndDate
is updated. I have a text box on the form that shows the ID of the original
record. So I thought the best way is to create a recordset using the
original records ID and then update the EndDate.
Any more help is appreciated.
 
Below is code that exists in a command button. How do I complete this code
to edit the record it finds and replace the StartDate with the
txb_StartDate.value?

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Meetings.MeetingID FROM Meetings WHERE
(((Meetings.MeetingID)=txb_ParentMeetingID.value));"
 
Below is code that exists in a command button. How do I complete this code
to edit the record it finds and replace the StartDate with the
txb_StartDate.value?

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Meetings.MeetingID FROM Meetings WHERE
(((Meetings.MeetingID)=txb_ParentMeetingID.value));"
 
Essentially like the following ... (although there might be some
hidden issues)

Dim strSQL As String

strSQL = "UPDATE Meetings SET StartDate = #" _
& Me.txb_StartDate & "# WHERE MeetingID=" _
& Me.txb_ParentMeetingID

CurrentDb.Execute strSQL, DbFailOnError

This assumes that MeetingID is numeric, if it is text then you would
need to wrap it in single quotes.

Note that date values need to be enclosed in #s and there is no need
to use .Value of a control since that is implied.

- Jim
 
Thanks for the help Jim.

Jim Allensworth said:
Essentially like the following ... (although there might be some
hidden issues)

Dim strSQL As String

strSQL = "UPDATE Meetings SET StartDate = #" _
& Me.txb_StartDate & "# WHERE MeetingID=" _
& Me.txb_ParentMeetingID

CurrentDb.Execute strSQL, DbFailOnError

This assumes that MeetingID is numeric, if it is text then you would
need to wrap it in single quotes.

Note that date values need to be enclosed in #s and there is no need
to use .Value of a control since that is implied.

- Jim
Click to expand...
 
Back
Top