Having a Problem with a Button Function on my form

[Guest]

Hi,

I have a form with a button at the bottom. This button is used to run a
data import function. When the button is clicked on, a call is made to a
form which prompts for the name of the input file, then calls the procedure
to run that actually imports the data.

Upon completion of the Import Function, I would like the form to load with
the correct record and data of what was just loaded. My key for looking up
records is RecordLookUpID. I have Global Variable called TempRecordID
which holds the record number generated (or found if the record already
exists in the table and data is just being changed) in the Import Process.

I don't know if the Findrecord code even works, because as soon as you click
the button, it tries to run the Findrecord code before the Openform and
import process is run.


My Button code is as follows for an On Click Event:

DoCmd.OpenForm "File_Prompt_MTSMat_Form"

' Find the newly imported record and load on Form.

DoCmd.FindRecord RecordLookUpID = TempRecordID


What am I doing wrong? I thank you for any help you can give. I must use
this same type of process on several forms.

 

[Steve Schapel]

Kmh,

You appear to be using an incorrect syntax for the FindRecord method.
Should be just like this...
DoCmd.FindRecord TempRecordID

However, for this to work, you would need to set the focus to the
RecordLookUpID control on the form first. So the code using FindRecord
would be...
DoCmd.OpenForm "File_Prompt_MTSMat_Form"
Forms!File_Prompt_MTSMat_Form!RecordLookupID.SetFocus
DoCmd.FindRecord TempRecordID

However, most likely this will serve your purpose better...
DoCmd.OpenForm "File_Prompt_MTSMat_Form", , , "RecordLookUpID =" &
TempRecordID

 

[Guest]

Steve,

I appreciate you getting back to me on this issue, but I think I am more
confused now.

I don't have a control on my "File_Prompt_MTSMat_Form" form for RecordID.
This form pops up and uses a dialog box to help the user browse their system
for the correct import file. Once found, this form then calls a module that
strips out the Record ID and it's associated data from the import file and
stores it in the appropriate tables. Then when this process is complete the
File_Prompt form closes and the user is looking at the Main Add/Edit/Delete
Form. What I would like to happen is for this Main Form to now go to and
display the record from the Import process.

So, I'm not sure how I could call the Openform with the Record ID or refer
to it on
MTS_Form if it's not there. Can I run my Module and then have an invisible
RecordID field filled in with the correct data at then of the module before
the MTS Prompt is closed? But if the Prompt form closes how can my Main form
do the following commands?

Forms!File_Prompt_MTSMat_Form!RecordLookupID.SetFocus
DoCmd.FindRecord TempRecordID

I am confused.

 

[Steve Schapel]

Kmh,

Assuming the ID field is part of the Record Source of the form, you can
use it as part of the Where Condition argument of the OpenForm method,
it doesn't need to be represented by a control on the form. I normally
put a hidden control on the form in such an instance anyway. The
FindRecord method would require there to be a control on the form. If
the ID field is not included in the form's record source table/query,
then I have misunderstood!

 

[Guest]

Okay. I think I understand a bit of what you are saying, but I am not
getting it to work with my setup, so let me re-word what I am trying to do
with more details.

My main form call Material_Main_Form is based on the Material Table which
has a RecordLookUpID field. I do have an invisible RecordLookUpID on my
Material_Main_Form.

I have a button on the Material_Main_Form that calls the MTS_Prompt.

The MTS_Prompt form just contains the dialog box fields required to lookup a
file name. There is no table or RecordLookUpID associated with this form.
It contains an unbound filelist box as part of the file browse dialog. Now,
once the user has found the filename and clicks on "go ahead and import
button" the MTS_Prompt form calls a VBA Module that does it's importing data
thing. Once this is completed, the MTS_Prompt form closes and control or
focus is returned to the Material_Main_Form.

This is when I would like to have that record just imported appear on the
Material_Main_Form.

Sorry to be a pain and I really appreciate the help.

 

[Steve Schapel]

Kmh,

Ok, so the newly imported record is identified by its RecordLookUpID
being held in the TempRecordID variable. Have I got that right? Well,
there are a number of approaches that could be taken here. I would say
one of these at the end of the importing code...
DoCmd.OpenForm "Material_Main_Form", , , "RecordLookUpID =" & TempRecordID

or...
With Forms!Material_Main_Form
.Filter = "RecordLookUpID =" & TempRecordID
.FilterOn = True
End with

or...
DoCmd.SelectObject acForm, "Material_Main_Form"
Forms!Material_Main_Form!RecordLookUpID.SetFocus
DoCmd.FindRecord TempRecordID

The 3rd approach would involve setting the Visible property of
RecordLookUpID to Yes.

All 3 approaches may need a Requery applied to the Material_Main_Form
form after the data import - I'm not 100% sure without trying it.
However, the 1st approach would work smoothly if the Material_Main_Form
form can be closed at the point where the MTS_Prompt form is opened.

 

[Guest]

Okay, we're getting closer. Setting the filter is working, but the problem
with setting a filter like that is, the user is then unable to move to
another record in the Material Form. Is there a way to turn off the filter
when they click on a combo box lookup control so they can move to another
record?

I really appreciate all your help.

 

[Steve Schapel]

Kmh,

DoCmd.ShowAllRecords

 

[Guest]

Thanks for your help. I am using option 2:

With Forms!Material_Main_Form
.Filter = "RecordLookUpID =" & TempRecordID
.FilterOn = True
End with


This works great!

I added the DoCmd.ShowAllRecords as the first line in the FindRecord Combo
Box After_Update and this works.

The only issue I see, is that the DoCmd.ShowAllRecords is always run even
though it only needs to be run once, after the Import function. This
wouldn't be such an issue, but the screen flashes everytime you use the
combobox and I don't like that. It looks like their is a coding problem.

Do I have this command in the wrong place? Or Is there a way to check to
see if a filter is set and if so, run the ShowAllRecords commane, otherwise
don't run it?

Thanks.

 

[Steve Schapel]

Kmh,

I think this should do it...
If Me.FilterOn Then
DoCmd.ShowAllRecords
End If

 

[Guest]

Works like a charm. Thanks so much for all your help.