form closing problem

I

ISmith

Hi,

I am having a problem closing a form. If I open a 'new record form' and try
and close it immediatly (no data entered) I get "you cannot add or change a
record because a related record is required in table 'table name'.

The same happens if I try and closing the form after submitting a record. I
am using Form_Current to SetFocus to a particular text field, before I close
the form I am trying to change the default value of a text field using
Form_Unload (which doesn't save because after the first message Access 02
complains of problems saving the record).

Any thoughts ?

Ian
 
A

Allen Browne

As you found, Form_Unload is too late to make a change.

Use Form_BeforeUpdate to alter any value needed. If the form is not dirty,
it will not fire, and so the form will close without incident.

Make sure the code in Form_Current does not dirty the record, i.e. it should
not set the value of any control.
 
I

ISmith

Hi,

thanks for the response.
Use Form_BeforeUpdate to alter any value needed. If the form is not dirty,
it will not fire, and so the form will close without incident.
Click to expand...

I am already using that event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.TNum.DefaultValue = Me.TNum + 1
sATitle = Me.ATitle
sCTitle = Me.CTitle

End Sub

Private Sub Form_Current()

Me.ATitle = sATitle
Me.CTitle = sCTitle
Me.TNum.SetFocus

End Sub

What I am trying to attempt is to increment the value in the text field TNum
and copy whatever is in sATitle & sCTitle to comboboxes (or rather select
entries from the combobox). This is a shortcut to save entering that
information on the next new record. This info may be constant in batches,
ie say 10 records with the same info and then a change which remains for the
next 5 records etc.

It was all working okay until I wanted to set the default value back to 1 on
the form close.

Any thoughts - set the default value on form open ?

Ian
 
A

Allen Browne

Where are sATitle and sCTitle declared?

Why is TNum' s default value set to one more than the current TNum? If you
go back and edit an earlier number, won't this give you duplicates?

Why are ATitle and CTitle being dirtied every time you move to a record?
Won't this overwrite the correct values if you step back to a previous
record?

To me it would make more sense to use Form_BeforeInsert to drop the new
defaults in, because this event fires only for new records.
 
I

ISmith

Hi,
Where are sATitle and sCTitle declared?
Click to expand...

In the (general) (declarations) of the form.
Why is TNum' s default value set to one more than the current TNum? If you
go back and edit an earlier number, won't this give you duplicates?
Click to expand...

The form is only used for new record entry, duplicates are infrequent but
possible.
Why are ATitle and CTitle being dirtied every time you move to a record?
Click to expand...

What I am trying to achieve is some fields being 'sticky' - ie when a new
record is entered and another new record is desired the latter will contain
data from the former, easier to delete that info and enter new in those
fields once when a new batch is started than enter that info for every
record in the batch.
Won't this overwrite the correct values if you step back to a previous
record?
No

To me it would make more sense to use Form_BeforeInsert to drop the new
defaults in, because this event fires only for new records.
Click to expand...

Which part should be put into BeforeInsert ? If I change the BeforeUpdate
to BeforeInsert I get complaints on opening the form because of invalid use
of null - the variables are empty as are the fields at that stage.

Ian
 
A

Allen Browne

Ian, I suggest you either go with just the Default Value approach for all
fields, or else initialize the fields for a new record in Form_BeforeInsert.
You can still use Form_BeforeUpdate if you need to do any form-level
validation, but don't dirty the record on Form_Current.
 
I

ISmith

Hi again,
Ian, I suggest you either go with just the Default Value approach for all
fields, or else initialize the fields for a new record in
Form_BeforeInsert.
Click to expand...

I have tried that in

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.TNum.DefaultValue = Me.TNum + 1
Me.ATitle.DefaultValue = Me.ATitle
Me.CTitle.DefaultValue = Me.CTitle

End Sub

But I end up with #Name? in the comboboxes. It works fine for the textfield
though Any thoughts as to what I am doing wrong. I have tried looking in
help to understand the event order, but am having problems getting this to
work.

Thanks for all your help.

Ian
 
A

Allen Browne

It might be an issue with the data types.

If ATitle is a Text field, try:
If Not IsNull(Me.ATitle) Then
Me.ATitle.DefaultValue = """" & Me.ATitle & """"
End If
 
I

ISmith

It might be an issue with the data types.
If ATitle is a Text field, try:
If Not IsNull(Me.ATitle) Then
Me.ATitle.DefaultValue = """" & Me.ATitle & """"
End If
Click to expand...

The setting the textfield default value works fine (that is the field where
I am incrementing the value), but it is the two comboboxes which display the
"#name?"

Ian
 
A

Allen Browne

Which is the Bound Column of the combo?
In the RowSource, what is the Data Type of this field?

For example, if the Bound Column is a Number field, you cannot set the
Default Value to a text string.
 
I

ISmith

Which is the Bound Column of the combo?
1
In the RowSource, what is the Data Type of this field?
Click to expand...
RST: Table/Query
RS: SELECT [ATable].AName FROM [ATable];
RS: SELECT [CTable].CName FROM [CTable];

These are used as lookup tables.
For example, if the Bound Column is a Number field, you cannot set the
Default Value to a text string.
Click to expand...

In the tables they are set to Text.

Ian
 
A

Allen Browne

Okay, so you have a string as a default value for a Text field.
That sounds fair enough.

To debug the issue, ask Access what is in the combo.
Open the Immediate Window (Ctrl+G), and enter things like:
? Forms!Form1.ATitle.Value
? Forms!Form1.ATitle.DefaultValue
? Forms!Form1.ATitle.ControlSource
Does this approach give you a useful lead?


BTW, it would also be worth checking that the Name AutoCorrect boxes are
unchecked under:
Tools | Options | General
so that Access does not get confused about what you are talking about. More
info on this:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ISmith said:
Which is the Bound Column of the combo? 1

In the RowSource, what is the Data Type of this field?
Click to expand...
RST: Table/Query
RS: SELECT [ATable].AName FROM [ATable];
RS: SELECT [CTable].CName FROM [CTable];

These are used as lookup tables.
For example, if the Bound Column is a Number field, you cannot set the
Default Value to a text string.
Click to expand...

In the tables they are set to Text.

Ian
Click to expand...
 
I

ISmith

To debug the issue, ask Access what is in the combo.
Open the Immediate Window (Ctrl+G), and enter things like:
? Forms!Form1.ATitle.Value
? Forms!Form1.ATitle.DefaultValue
? Forms!Form1.ATitle.ControlSource
Does this approach give you a useful lead?
Click to expand...

Still struggling:

Private Sub Form_Open(Cancel As Integer)

Me.TNum.DefaultValue = 1

Me.ATitleCombo.DefaultValue = ""

Me.CTitleCombo.DefaultValue = ""

End Sub



Private Sub Form_BeforeUpdate(Cancel As Integer)



Debug.Print "Value: " & Forms![My Table].ATitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].ATitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].ATitleCombo.ControlSource



Debug.Print "Value: " & Forms![My Table].CTitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].CTitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].CTitleCombo.ControlSource



Me.TNum.DefaultValue = Me.TNum + 1



If IsNull(ATitleCombo.Value) Then

Else

sArtTitle = Me.ATitleCombo.Value

sAlbTitle = Me.CTitleCombo.Value

End If



Me.ATitleCombo.DefaultValue = sATitle

Me.CTitleCombo.DefaultValue = sCTitle



Debug.Print "Value: " & Forms![My Table].ATitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].ATitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].ATitleCombo.ControlSource



Debug.Print "Value: " & Forms![My Table].CTitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].CTitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].CTitleCombo.ControlSource



End Sub



Produces:



Value: Test123

DefaultValue:

ControlSource: ATitle



Value: Test456

DefaultValue:

ControlSource: CTitle





Value: Test123

DefaultValue: Test123

ControlSource: ATitle



Value: Test456

DefaultValue: Test456

ControlSource: CTitle





In addition, when I look in the property sheet for the comboboxes text
matching "Test456" and "Test123" is there, and appears identical to values
selectable from the comboboxes.



For the two comboboxes:

RST: Table/Query
RS: SELECT [ATable].AName FROM [ATable];
RS: SELECT [CTable].CName FROM [CTable];



For the form

RecordSource: My Table



I have noticed though that the fields that are acting as the controlsource
for the two comboboxes are from My Table and not from the ATable or CTable
that are being used as lookup tables.



The two lookup tables have only one field each containing the vaules which
appear in the comboboxes. Each lookup table has a one-to-many relationship.



TNum is 'sticking' & incrementing correctly, I get #name? still with both
combos. Any further thoughts ? Thanks for your patience and help. I
turned off the autocorrect as you suggested.



Ian
 
A

Allen Browne

What about the Form_BeforeInsert event?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ISmith said:
To debug the issue, ask Access what is in the combo.
Open the Immediate Window (Ctrl+G), and enter things like:
? Forms!Form1.ATitle.Value
? Forms!Form1.ATitle.DefaultValue
? Forms!Form1.ATitle.ControlSource
Does this approach give you a useful lead?
Click to expand...

Still struggling:

Private Sub Form_Open(Cancel As Integer)

Me.TNum.DefaultValue = 1

Me.ATitleCombo.DefaultValue = ""

Me.CTitleCombo.DefaultValue = ""

End Sub



Private Sub Form_BeforeUpdate(Cancel As Integer)



Debug.Print "Value: " & Forms![My Table].ATitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].ATitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].ATitleCombo.ControlSource



Debug.Print "Value: " & Forms![My Table].CTitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].CTitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].CTitleCombo.ControlSource



Me.TNum.DefaultValue = Me.TNum + 1



If IsNull(ATitleCombo.Value) Then

Else

sArtTitle = Me.ATitleCombo.Value

sAlbTitle = Me.CTitleCombo.Value

End If



Me.ATitleCombo.DefaultValue = sATitle

Me.CTitleCombo.DefaultValue = sCTitle



Debug.Print "Value: " & Forms![My Table].ATitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].ATitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].ATitleCombo.ControlSource



Debug.Print "Value: " & Forms![My Table].CTitleCombo.Value & vbCrLf & _

"DefaultValue: " & Forms![My Table].CTitleCombo.DefaultValue & vbCrLf & _

"ControlSource: " & Forms![My Table].CTitleCombo.ControlSource



End Sub



Produces:



Value: Test123

DefaultValue:

ControlSource: ATitle



Value: Test456

DefaultValue:

ControlSource: CTitle





Value: Test123

DefaultValue: Test123

ControlSource: ATitle



Value: Test456

DefaultValue: Test456

ControlSource: CTitle





In addition, when I look in the property sheet for the comboboxes text
matching "Test456" and "Test123" is there, and appears identical to values
selectable from the comboboxes.



For the two comboboxes:

RST: Table/Query
RS: SELECT [ATable].AName FROM [ATable];
RS: SELECT [CTable].CName FROM [CTable];



For the form

RecordSource: My Table



I have noticed though that the fields that are acting as the controlsource
for the two comboboxes are from My Table and not from the ATable or CTable
that are being used as lookup tables.



The two lookup tables have only one field each containing the vaules which
appear in the comboboxes. Each lookup table has a one-to-many
relationship.



TNum is 'sticking' & incrementing correctly, I get #name? still with both
combos. Any further thoughts ? Thanks for your patience and help. I
turned off the autocorrect as you suggested.



Ian
Click to expand...
 
I

ISmith

What about the Form_BeforeInsert event?

What did you have in mind ? I changed Form_BeforeUpdate into
Form_BeforeInsert and now #name? has dissapeared, but the combos are empty -
the debug has nothing for Value & DefaultValue.
Ian
 
A

Allen Browne

ISmith said:
What did you have in mind ?
Click to expand...

That was discussed in the 2nd and 3rd reply to this thread, Ian.

I think I will need to leave it with you from here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Prevent Duplicate Form Entries 0
First Subform record gets modified when closing form 3
Saving a record in subform 5
How to prevent form from closing 2
Separate Form 0
DMax on existing record to increment serialized # fails 1
Main \ Sub Form Data 1
Validation in child form 1

Top