How to update control's value before running the stored procedure?

[childrenfirst]

Hi,

I have a form which has two controls, txtViewReportNum and txtViewDescription, and a subform which shows results from a SQL stored procedure named s_ViewList. The problem with my vba code is that the string which execute and return values from the stored procedure always seems to execute before values are loaded into the two controls. However, the stored procedure is supposed to run AFTER values are loaded into the two controls, and the value in txtViewReportNum will be passed to the stored procedure as a parameter.

How do I stop the code below from being executed until values are loaded into txtViewList, and make sure that value in txtViewReportNum will be passed to the string below?

-----current string which execute the stored procedure---
'update subfPreviewNewList
Dim strReportNum As String
Dim strList As String

'update frmAuditList with the information for the selected list/report
strReportNum = Me.txtViewReportNum.Value
MsgBox strReportNum

strList = "EXEC s_ViewList '" & strReportNum & "'"
Me!subfPreviewNewList.Form.RecordSource = strList

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

Any advice will be greatly appreciated!!!

Happy holiday

 

[IGPCS]

i had the same proble couple of weeks ago and what i did i that i set 2 variables that got via vba code the same info the fields are suppose to get and by hte time th fields got the data my result was set in the result field took me a week t o think of this idea but it works and geuss what my boss was upset it took me a week longer but i told be happy its working LOL

try it and let me know

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[childrenfirst]

Hi IGPCS,

Thank you for the idea! I thought about that before but I got stuck when I tried to pass the result from the stored procedure executed in the first form, frmMain, to the subform in the second form, frmList.


Part of my current code in the first form:

Dim strList AS String

strList = "EXEC s_ViewAuditList '" & strReportNum & "'"
Me!subfPreviewNewList.Form.RecordSource = strList

The problem is that the line, Me!subfPreviewNewList..., is not working. I am unable to show the return the result of the stored procedure in the second form although the stored procedure is executed when user is still seeing the first form. Please advise how I can revise this line of code in order to pass the results to the subform in the second form.

Thank you!!!!!

 

[PWise]

childrenfirst

in what event do you run this code

 

[hwkranger]

CF,

I would suggest investing in a process management examination. You are probably running your usp in the "onload", "on open", "on currrent" event.. Or maybe the source of your subform is the sp resullts...

My guess is that it's the "source of the subform".. try setting the source of your subform in the current event of the main form. This will fix your problem.

This will create an issue with process control, and 'expected trouble shooting' progression methodology -- unless you have defined a standard for how forms and subforms should load and where things "should" happen, this may or may not be counter developed in the terms of your shop processes flow.

I hope this helps.



Randall Vollen
Meryl Lynch

 

[childrenfirst]

I run these code in a private sub on click of a command button in the first form.

 

[hwkranger]

I run these code in a private sub on click of a command button in the first form.

private/public are access rights to member attributes. pwise requested the EVENT name. An example of an event name is "On Mouse click" or "On Open"...



Randall Vollen
Meryl Lynch

 

[hwkranger]

Correction I misread the last statement.

My apologies... wow I'm an idiot today.

I skipped the whole "click of a command button" and thought -- Wow i'm confused.

If you're doing this on a click then -- those should already be loaded.

Randall Vollen
Meryl Lynch

 

[childrenfirst]

Hi Randall,

Thank you for your idea I guess I need to fix the data source in the properties of the subform. Can you make some suggestion? What should I enter in RecordSource in the subform's Properties in order to show the results from the executed stored procedure?

Thanks a bunch!

 

[IGPCS]

well you didnt put in the line the field name

Me!subfPreviewNewList.Form.FieldName = strList

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[childrenfirst]

Hi IGPCS,

Thank you for the suggestion! I replaced my Me!subfPreviewNewList.Form.RecordSource code with your suggestion but it did not work.

The stored procedure returns values for multiple fields, and that's why I wrote the vba code to pass records to the subform instead of value to a specific field.

Happy holiday!!

 

[hwkranger]

Update:

I tested this same approach and it works for me...

And: Ignore my mispelled company name. Seems I'm the reciever of a joke from a few weeks ago... lol

Randall Vollen
Merrill Lynch

 

[childrenfirst]

Hi Randall,

Could you post the exact code you tried that worked since there are different suggestions to my question and I am a little bit confused now...?

Thank you very much!

 

[IGPCS]

or try just

Form_subfPreviewNewList.FieldName = strList

try to break the code before this line and check when you move your mouse over it what the result of the StrList

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[childrenfirst]

Hi IGPCS,

I added a line of code, MsgBox strList to show the value of strList, and the message box shows

Exec s_ViewAuditList 2
(Note: 2 is based on the report number I selected on the first form)

So, it seems that strList is grabbing and passing the parameter correctly to the stored procedure.

To apply the line of code you suggested, should I replace .FieldName with an actual name of the field in the subform? Also, when I ran my line of code,
Me!subfPreviewNewList.Form.RecordSource = strList, I got an error message saying that the field, subfPreviewNewList, cannot be found. I think that the problem is that the code is stored in the event for the first form and the subform is located in the second form. Is there anyway that I can point to the second form and then the subform in the second form? For example, is there something like:

Me!frmNameofSecondForm.subfPreviewNewList....

Thank you very much for all your help You surely have lots of holiday spirit!!

 

[IGPCS]

yes you need to replace field name with the name of you field

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[childrenfirst]

Hi IGPCS,

Thank you for clarifying it for me. I have a silly question: the stored procedure returns multiple values (for example, report number, employee name, record date and etc.), and there are also multiple fields in the subform (for example, report number, employee name, record date and etc.). So, how do I know which field name I should pick to replace .FieldName in the line of code, Me!subfPreviewNewList.Form.FieldName = strList?

Thanks a bunch!

 

[IGPCS]

well you should know in what field you want to put it isnt that what you needed is to have info from the first form on the second form ? well if you dont just add in the table and then in the form the field you need

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[childrenfirst]

Oh my goodness. It's finally fixed...

I modified the line of code to


Forms!frmAuditList.subfPreviewNewList.Form.RecordSource = strList

and the system was able to find the second form and the subform and return the records, strList, to the subform.

Thank youuuuuuu all for brainstorming with me. I really appreciate it

Happy holiday!

 

[IGPCS]

Your welcome and happy holidays

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY