Help with order of operations

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I have a form that is opened by a command button on a main form. I want some
items to be prepopulated on open. The problem I am running into is that
entereing the value starts a new record on the source table. If the form is
closed I get an error because part of the key is still empty.

How can I populate the form without actually writing to the record until the
form is closed?
 
In order to better help you, some additional information would be helpful.
Looking at your question, I'd like to see your code. Without it, I'm guessing
as to what is currently happening.

Typically you would have the button open the form and then set the value of
each control you want populated. The following would be a starting point:

forms!YourFormName.YourControlName = The value you want populated in the
control
 
Dim strErr As String
Dim intMilestoneID As Integer
Dim strSQL As String
Dim strThisSubName As String
Dim strWhere As String

' Declarations
intMilestoneID = DLookup("[MilestoneID]", "tblMilestones", "MilestoneName =
'" & strMilestoneName & "'")
strThisSubName = "OpenMilestones"

'Save first
If Forms!frm_Projects.Dirty Then Forms!frm_Projects.Dirty = False

'If record is not empty, show report
If Forms!frm_Projects.NewRecord Then
MsgBox "There is no project selected"
Else
If IsNull(varMilestoneValue) Then
'If myMilestone is empty, open to enter myMilestone.
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere, acFormAdd
Forms!frmProjectMilestones.cboMilestoneID = intMilestoneID
Else
'Open and filter on that myMilestone
strWhere = "[ProjectID] = " & intProjectID & " AND [MilestoneID] = "
& intMilestoneID
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere
End If
End If


That is basically what I am doing, but the act of setting the control's
value is creating a record in the associated table so if I open the form,
decide not to update the record and close the form, I get an error for
missing key values.
 
Setting the values of the controls isn't saving the data to the table;
closing the record is saving the data. When you leave a record (by closing a
form or moving to the next record) all of the data on the record is written
to the table.

If you want to prepopulate values on the form, it automatically has values
to save if you leave the record. What you would need is an undo button to
clear the values before you save. You could also have code check for all
required fields and if not all fields have values, the code can either undo
the data or bring you back to the form and set focus one of the required
fields.

You could either use the .undo method or set the values to Null.

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

HTH

Don''''t forget to rate the post if it was helpful!

My email address is invalid.
Please reply to newsgroup only so that others may benefit.


MeSteve said:
Dim strErr As String
Dim intMilestoneID As Integer
Dim strSQL As String
Dim strThisSubName As String
Dim strWhere As String

' Declarations
intMilestoneID = DLookup("[MilestoneID]", "tblMilestones", "MilestoneName =
'" & strMilestoneName & "'")
strThisSubName = "OpenMilestones"

'Save first
If Forms!frm_Projects.Dirty Then Forms!frm_Projects.Dirty = False

'If record is not empty, show report
If Forms!frm_Projects.NewRecord Then
MsgBox "There is no project selected"
Else
If IsNull(varMilestoneValue) Then
'If myMilestone is empty, open to enter myMilestone.
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere, acFormAdd
Forms!frmProjectMilestones.cboMilestoneID = intMilestoneID
Else
'Open and filter on that myMilestone
strWhere = "[ProjectID] = " & intProjectID & " AND [MilestoneID] = "
& intMilestoneID
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere
End If
End If


That is basically what I am doing, but the act of setting the control's
value is creating a record in the associated table so if I open the form,
decide not to update the record and close the form, I get an error for
missing key values.

Maverick said:
In order to better help you, some additional information would be helpful.
Looking at your question, I'd like to see your code. Without it, I'm guessing
as to what is currently happening.

Typically you would have the button open the form and then set the value of
each control you want populated. The following would be a starting point:

forms!YourFormName.YourControlName = The value you want populated in the
control
Click to expand...
Click to expand...
 
..undo is what i was looking for, thanks.

Maverick said:
Setting the values of the controls isn't saving the data to the table;
closing the record is saving the data. When you leave a record (by closing a
form or moving to the next record) all of the data on the record is written
to the table.

If you want to prepopulate values on the form, it automatically has values
to save if you leave the record. What you would need is an undo button to
clear the values before you save. You could also have code check for all
required fields and if not all fields have values, the code can either undo
the data or bring you back to the form and set focus one of the required
fields.

You could either use the .undo method or set the values to Null.

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

HTH

Don''''t forget to rate the post if it was helpful!

My email address is invalid.
Please reply to newsgroup only so that others may benefit.


MeSteve said:
Dim strErr As String
Dim intMilestoneID As Integer
Dim strSQL As String
Dim strThisSubName As String
Dim strWhere As String

' Declarations
intMilestoneID = DLookup("[MilestoneID]", "tblMilestones", "MilestoneName =
'" & strMilestoneName & "'")
strThisSubName = "OpenMilestones"

'Save first
If Forms!frm_Projects.Dirty Then Forms!frm_Projects.Dirty = False

'If record is not empty, show report
If Forms!frm_Projects.NewRecord Then
MsgBox "There is no project selected"
Else
If IsNull(varMilestoneValue) Then
'If myMilestone is empty, open to enter myMilestone.
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere, acFormAdd
Forms!frmProjectMilestones.cboMilestoneID = intMilestoneID
Else
'Open and filter on that myMilestone
strWhere = "[ProjectID] = " & intProjectID & " AND [MilestoneID] = "
& intMilestoneID
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere
End If
End If


That is basically what I am doing, but the act of setting the control's
value is creating a record in the associated table so if I open the form,
decide not to update the record and close the form, I get an error for
missing key values.

Maverick said:
In order to better help you, some additional information would be helpful.
Looking at your question, I'd like to see your code. Without it, I'm guessing
as to what is currently happening.

Typically you would have the button open the form and then set the value of
each control you want populated. The following would be a starting point:

forms!YourFormName.YourControlName = The value you want populated in the
control

:

I have a form that is opened by a command button on a main form. I want some
items to be prepopulated on open. The problem I am running into is that
entereing the value starts a new record on the source table. If the form is
closed I get an error because part of the key is still empty.

How can I populate the form without actually writing to the record until the
form is closed?
Click to expand...
Click to expand...
Click to expand...
 
You are quite welcome.

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

HTH

Don''''t forget to rate the post if it was helpful!

My email address is invalid.
Please reply to newsgroup only so that others may benefit.


MeSteve said:
.undo is what i was looking for, thanks.

Maverick said:
Setting the values of the controls isn't saving the data to the table;
closing the record is saving the data. When you leave a record (by closing a
form or moving to the next record) all of the data on the record is written
to the table.

If you want to prepopulate values on the form, it automatically has values
to save if you leave the record. What you would need is an undo button to
clear the values before you save. You could also have code check for all
required fields and if not all fields have values, the code can either undo
the data or bring you back to the form and set focus one of the required
fields.

You could either use the .undo method or set the values to Null.

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

HTH

Don''''t forget to rate the post if it was helpful!

My email address is invalid.
Please reply to newsgroup only so that others may benefit.


MeSteve said:
Dim strErr As String
Dim intMilestoneID As Integer
Dim strSQL As String
Dim strThisSubName As String
Dim strWhere As String

' Declarations
intMilestoneID = DLookup("[MilestoneID]", "tblMilestones", "MilestoneName =
'" & strMilestoneName & "'")
strThisSubName = "OpenMilestones"

'Save first
If Forms!frm_Projects.Dirty Then Forms!frm_Projects.Dirty = False

'If record is not empty, show report
If Forms!frm_Projects.NewRecord Then
MsgBox "There is no project selected"
Else
If IsNull(varMilestoneValue) Then
'If myMilestone is empty, open to enter myMilestone.
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere, acFormAdd
Forms!frmProjectMilestones.cboMilestoneID = intMilestoneID
Else
'Open and filter on that myMilestone
strWhere = "[ProjectID] = " & intProjectID & " AND [MilestoneID] = "
& intMilestoneID
DoCmd.OpenForm "frmProjectMilestones", acNormal, , strWhere
End If
End If


That is basically what I am doing, but the act of setting the control's
value is creating a record in the associated table so if I open the form,
decide not to update the record and close the form, I get an error for
missing key values.

:

In order to better help you, some additional information would be helpful.
Looking at your question, I'd like to see your code. Without it, I'm guessing
as to what is currently happening.

Typically you would have the button open the form and then set the value of
each control you want populated. The following would be a starting point:

forms!YourFormName.YourControlName = The value you want populated in the
control

:

I have a form that is opened by a command button on a main form. I want some
items to be prepopulated on open. The problem I am running into is that
entereing the value starts a new record on the source table. If the form is
closed I get an error because part of the key is still empty.

How can I populate the form without actually writing to the record until the
form is closed?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top