DoCmd.Close form with acSaveNo still prompts to save subform

[bismuth83]

I have a form that has a subform which points to a saved query in
datasheet view. Prior to opening this form, I reset the SQL of the
query using a querydef, so that the subform fields end up being
dynamic. This all works fine unless I try resizing the column widths
in the datasheet. Then, when I close the form, I get prompted to save
the query. If I click Yes or Cancel, when I reopen the form, I get a
"The search key was not found in any record" error. The error goes
away after I delete and recreate the saved query, but comes back if
the process is repeated.

I've tried using a command button to close the form, using
"DoCmd.Close acForm, Me.Name, acSaveNo," but I still get prompted to
save the query. Also tried using "DoCmd.SetWarnings False" to avoid
the prompt altogether, which works, but it auto saves the form when
the column sizes are changed, and gives me an error.

 

[strive4peace]

Hi bismuth83 (what is your name?)

Instead of putting the query on the form directly, make a form to hold
the query and put that form on as a subform. Even if this does not fix
this specific problem, you have a lot more control if the query is
contained in a subform

The reason you are getting prompted to save is because you are changing
the layout of the columns -- maybe, if the query is in a subform, it
will be the subform that holds the column widths.

As a personal preference, I do not ever use Datasheet view for forms,
there is just not enough control.

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[bismuth83]

Hi, thanks for the reply.

I tried your suggestion, which does work. There are no errors when I
resize the columns. A problem, though, is that since the query SQL is
dynamic, the fields are always changing. I have to add textboxes to
view data, but I guess in order to use this solution, I'll have to
figure out how to readd each textbox each time.

Ann

 

[strive4peace]

Hi Ann,

great!

If the fields change while the form is open, do this:

- make the subform control not visible
- set the subform SourceObject to a dummy form that has the same
LinkchildFields on it
- rewrite the query
- set the subform SourceObject back to the form you want
- make the subform control visible

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[bismuth83]

The source isn't changed while the form is open, so that part is
fine. I think it's more a matter of somehow tracing through the form
recordsource and manually assigning the new field names to a new set
of textboxes.

 

[strive4peace]

Hi Ann,

can you give 2 exampes of different SQL statements that might be used
for the subform?

Want to see a little bit about what kind of data is there and how you
are dealing with different fields and different numbers of fields --
then I will give you some suggestions for labeling the columns

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[bismuth83]

The form is part of an import utility, and it lets you compare the
same record on the server and in your remote file. From a list of
possible conflicts, you click a detail button, and this form shows you
how that record was changed. There may be conflicts on different
tables, so I update the union query SQL each time the form is opened.

Here are some simplified versions of possible SQL:

(SELECT "Server Data:" as Location, DataTable.ID,
DataTable.Description, DataTable.CreationDate, DataTable.EditDate FROM
Brands as DataTable WHERE DataTable.ID=24)
UNION ALL
(SELECT "Remote Data:" as Location, DataTable.ID,
DataTable.Description, DataTable.CreationDate, DataTable.EditDate FROM
[C:\RemoteFile.mdb].Brands as DataTable WHERE DataTable.ID=24)

(SELECT "Server Data:" as Location, DataTable.SampleID,
DataTable.ListID, DataTable.ListValue, DataTable.Comment,
DataTable.CreationDate, DataTable.EditBy, DataTable.EditDate FROM
SampleList as DataTable WHERE DataTable.SampleID=24 AND
DataTable.ListID=13)
UNION ALL
(SELECT "Remote Data:" as Location, DataTable.SampleID,
DataTable.ListID, DataTable.ListValue, DataTable.Comment,
DataTable.CreationDate, DataTable.EditBy, DataTable.EditDate FROM [C:
\RemoteFile.mdb].SampleList as DataTable WHERE DataTable.SampleID=24
AND DataTable.ListID=13)

 

[strive4peace]

Hi Ann,

here is some code you can modify -- then trigger it differently once you
get it working

Form has textboxes with Names:
Text0
Text1
....
Text9

and corresponding Labels with Names:
Label0
Label1
....
Label9

Name of query --> qDataSource

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
Private Sub Form_Open(Cancel As Integer)

On Error GoTo Proc_Err

Dim db As DAO.Database _
, qdf As DAO.QueryDef

Dim i As Integer _
, mLastControlIndex As Integer

mLastControlIndex = 9

Set db = CurrentDb

Set qdf = db.QueryDefs("qDataSource")

For i = 0 To mLastControlIndex

If i < qdf.Fields.Count Then
Me("Label" & i).Caption = qdf.Fields(i).Name
Me("text" & i).ControlSource = qdf.Fields(i).Name
Me("text" & i).ColumnHidden = False
Else
Me("text" & i).ControlSource = ""
Me("text" & i).ColumnHidden = True
End If
Next i


Proc_Exit:
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " Form_Open: " & Me.Name

Resume Proc_Exit
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[bismuth83]

Hello,

I tried your code, and it works perfectly. Granted, I'll have to
remember if any of my tables go over the max number of fields, but
yours is a much cleaner solution than the way I was trying.

Thanks for all your help!

 

[strive4peace]

Hi Ann,

you are welcome

there is no harm in adding more Text#/Label# pairs -- just increase
mLastControlIndex = 9
in the code to match


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*