Create Table with wrong data

[bw]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

Thanks,
Bernie

 

[Marshall Barton]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

I believe the DoCmd save record is saving the main form
record. DoCmd does not allow you to be specific as to what
you want to save. Try this instead:

Forms![tblAirports]![tblFlightNumber
Subform].Form.Dirty=False

Note: If this code is running in the main form, then you can
use this shorter equivalent:

Me.[tblFlightNumber Subform].Form.Dirty=False

 

[John Vinson]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

Thanks,
Bernie

I don't understand why you would want to run a MakeTable query in this
circumstance, at all! What is the purpose of this button?

Are you generating a report based on the data in that table? If so,
might it not be simpler just to base the report on a Select query?
What record is being saved (i.e. what is this table's Recordsource),
and why? And could you post the SQL of tblFlightNumberQuery?

John W. Vinson[MVP]

 

[bw]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

I believe the DoCmd save record is saving the main form
record. DoCmd does not allow you to be specific as to what
you want to save. Try this instead:

Forms![tblAirports]![tblFlightNumber
Subform].Form.Dirty=False

Note: If this code is running in the main form, then you can
use this shorter equivalent:

Me.[tblFlightNumber Subform].Form.Dirty=False

Thanks Marshall

I used the code you specified for use in the main form, and it works fine.

However, after looking at Jon Vinson's comments, I am rethinking what I'm
doing. He probably has a good point. Please see my response to him.

Again, Thanks
Bernie

 

[bw]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

Thanks,
Bernie

------------------------------------------------------------------------------------------

I don't understand why you would want to run a MakeTable query in this
circumstance, at all! What is the purpose of this button?

My intent was to use the main and subforms to allow for generating several
flight schedules. The button will increment the flight information, so that
I know if this was the first scheduled flight, second scheduled flight, etc.

Are you generating a report based on the data in that table?

Yes, which is why I need to know which flight this will be.

If so, might it not be simpler just to base the report on a Select query?

Now that you mention it, I suppose this is true. I haven't taken the time
yet to test what you are suggesting, but it sound okay to me.

What record is being saved (i.e. what is this table's Recordsource),
and why?

The record that is being saved is a combination of unbound fields from the
main form, and the specific record that was selected on the subform. The
Recordsource of the subform is as follows:

SELECT tblFlightNumber.FltNum, tblFlightNumber.AirlineID,
tblFlightNumber.FromAirport, tblFlightNumber.ToAirport, tblFlightNumber.Via,
tblFlightNumber.DepartTime, tblFlightNumber.ArrivalTime,
tblFlightNumber.FltDate, tblFlightNumber.FltLeg FROM tblFlightNumber ORDER
BY tblFlightNumber.DepartTime;

And could you post the SQL of tblFlightNumberQuery?

The SQL of tblFlightNumberQuery is as follows:

SELECT tblFlightNumber.FltNum, tblFlightNumber.AirlineID,
tblFlightNumber.FromAirport, tblFlightNumber.ToAirport, tblFlightNumber.Via,
tblFlightNumber.DepartTime, tblFlightNumber.ArrivalTime,
tblFlightNumber.FltDate, tblFlightNumber.FltLeg INTO SelectedRecords
FROM tblAirlines INNER JOIN tblFlightNumber ON tblAirlines.AirlineID =
tblFlightNumber.AirlineID
WHERE (((tblFlightNumber.FltLeg)>0));

John W. Vinson[MVP]

Thanks for your questions. Maybe you can lead me to greener pastures with
my new personal project.
Bernie

 

[Marshall Barton]

Private Sub SelectRecord_Click()
On Error GoTo Err_SelectRecord_Click
Dim stDocName As String

stDocName = "tblFlightNumberQuery"
Forms![tblAirports]![tblFlightNumber Subform]![FltDate] =
Forms![tblAirports]![TravelDate]
Forms![tblAirports]![tblFlightNumber Subform]![FltLeg] = 1
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery stDocName, acNormal, acEdit 'THIS IS A CREATE TABLE
QUERY

Exit_SelectRecord_Click:
Exit Sub

Err_SelectRecord_Click:
MsgBox Err.Description
Resume Exit_SelectRecord_Click

End Sub

The button for this procedure is on the main form. When I click on the
button, the data is not updated until AFTER the Create Table query is run.

How do I get the selected record in the Subform to be saved/updated before
the Create Table Query is executed?

"Marshall Barton" wrote
I believe the DoCmd save record is saving the main form
record. DoCmd does not allow you to be specific as to what
you want to save. Try this instead:

Forms![tblAirports]![tblFlightNumber
Subform].Form.Dirty=False

Note: If this code is running in the main form, then you can
use this shorter equivalent:

Me.[tblFlightNumber Subform].Form.Dirty=False

I used the code you specified for use in the main form, and it works fine.

However, after looking at Jon Vinson's comments, I am rethinking what I'm
doing. He probably has a good point. Please see my response to him.

Yes, I see that John is trying to address the bigger issue
instead of just answering your out of context question. I
don't want to confuse the issue by interfering with his
ideas, but I will add the generalization that using a
temporary table is rarely the right way to do anything.