How can I use the value of the current record in a query? 2

[Mabjro]

I have created an instance of a form using the following procedure;

Public Sub OpenFormSelectReportByTitle()
Dim frm1 As Form
Set frm1 = Form_frmSelectReport
frm1.RecordSource = "qryFindReportByTitle"
MsgBox frm1.RecordSource
frm1.Visible = True

End Sub

How can I use the current value of a field on frm1 as search criteria in a QBF? (query by form)
The following does not work.
form!frmSelectReport!txt1 or
form!frm1!txt1

Thank you,
Mabjro

 

[vbajock]

Set frm1 = Form_frmSelectReport

form!frmSelectReport!txt1

looks like a syntax error.
I wish people would not post things like "it doesn't work" or "it gives me an error"
What error?
What line of code?

 

[Mabjro]

vbajock

I asked a question;
"How can I use the current value of a field on frm1 as search criteria in a QBF? (query by form)"

I have shown you what I have tried in the query as criteria;
"form!frmSelectReport!txt1 and I have tried form!frm1!txt1"

My above attempts do not query the records based on the data in field txt1 on the form. (The problem is that I am working with an instance of the form not the form itself and I need to know if there is a way to reference it for the query)

If there were a syntax error in the query, there would be an error message. This is not the case, so I have no error message to tell you about.

If you really want to help me, you would just politely ask me if there were any error messages. Don't bother now, it’s not worth inflating your ego any more then it already is. Please do not post to this thread again so that people that are really here to help are not distracted from the subject. You are not at work and you have nothing to gain by being arrogant.

Thank you,
Mabjro

 

[Mabjro]

P.S.

Just so you know why you struck a nerve with me. I put a lot of time into my questions. You need to understand that sometimes trying to word the question can be almost as difficult as solving the problem. I am not even sure if anyone understands what I mean by "an instance of a form". Do I need to explain that in more detail? All I can do is throw the question out there and hopefully get some constructive dialog going. As for "it doesn't work" and "it gives me an error" I know where you are coming from in some cases. Don't take it out of context and look at the rest of the question to determine if the person has put effort into their question before you blast them. Now I need to start a new thread because this one will go now where.

Thanks

 

[cmmrfrds]

You can always reference a function in a query either built-in or one of your own. I usually make public variables and return them through a function.

Like so.
Standard module.
Public myvar as string

Public Function ReturnMyvar() as string
ReturnMyvar = myvar
End Function

In the Form.
In afterupdate event of field.
myvar = Me.Myfield

In the Query.
Select * from mytable where myfield = ReturnMyvar()

 

[Mabjro]

Thank you cmmrfrds, I think you are right on track. I will have to work with this a little, but I think this approach will work. I will post back before the week is out to let you know how I made out.

Thank you,

Mabjro

 

[vbajock]

Sorry about your struck nerve. It was not aimed at you personnally, but at the forum as a whole. It just needs to be said that when people post error related problems they need to be as specific as possible about error numbers and error descriptions. Otherwise it takes about three posts to get that information. Since I am giving my time on this forum away for free, it bugs me when time is wasted with trying to interpret generalized information.

I use a slight variation on cmmrfrds method by using two functions

Public myvar as string

Function SetMyvar(byval somevar as string) as string
myvar=somevar
End Function

Function GetMyvar() as string
GetMyvar = myvar
End Function

This keeps the scope of the variable localized to that module.

Anywhere the value changes

Call SetMyvar(Me!Myfield)

Anywhere the value is needed, including a recordsource,

whatever=GetMyvar()

 

[Mabjro]

vbajock,

You did strike a nerve, but that is water under the bridge. Thank you for your professionalism in your last post. By trade, I am and AutoCAD expert of 15 years. I also donate time to help others and I also get frustrated at times, so I know where you are coming from. I lot of times, in order to ask a good question, you almost have to know the answer, and in this case I could not figure out how to word it. It was big of you to look into my question despite the bickering and I appreciate it. I am going to try yours and cmmrfrds suggestions and post back my results sometime over the weekend.

Thank you,
MABJRO

 

[Mabjro]

cmmrfrds and vbajock,
I have tried both of your methods with some success. I learned a lot from your posts but I am still having a problem with referencing frm1. I will try to explain.

In my code I have the following;
Set frm1 = Form_frmSelectReport
and I also have;
frm1.Visible = True

This code makes visible frm1, a temporary version of frmSelectReport. When the user closes frm1, it is gone for good. I have found this to be advantagious when making programmatic design changes to the form. For example, I wanted to temporarily change the recordsource of frmSelectReport, so I used the following line of code;
frm1.RecordSource = "qryFindReportByTitle". The original form remains unchanged but the instance (frm1) is changed. No matter how the user closes frm1, they are not prompted to save changes. There is no way to go into design view of frm1 either because it is just an instance of frmSelectReport. In fact I can have multiple instances of frmSelectReport open at once using this method. This is all great, but I still cannot point to the instance (frm1) instead of the original in a query. The variations of the method that both of you have suggested (very helpfull by the way) alway appear to point at the original form instead of the instance. I am lead to believe this because I never get an error, but I end up with no found records. If the query were looking at frmSelectReport, no found records would be found because it is not open.

Thank you,
Jonathan

 

[cmmrfrds]

If I understand correctly, in the code when you are setting a reference to frm1 at this point also update the variable that will be returned from the function. So, when the query is run it will reference the value through the function.

I assume the query is something like this.

Select * from tab where id = ReturnID()

In order for us to fully understand, you may need to paste in the query and where you are loading the variable.

 

[Mabjro]

cmmrfrds,
Here is the query. I hope it sheds some light on my question.

SELECT tblInterconnectionDiagram.*, tblInterconnectionDiagram.Description
FROM tblInterconnectionDiagram
WHERE (((tblInterconnectionDiagram.Description)=Returnmyvar()));

Thank you,
Mabjro

 

[vbajock]

Have you tried this?
frm1.RecordSource = "qryFindReportByTitle" is the statement that is giving you fits. Using the two functions I supplied, the solution may be to not use a stored query, but a SQL string as your record source:

call SetMyvar(toSomeVar)

sql="SELECT * FROM sometable WHERE criteria = '" &+GetMyVar() +&"'"
frm1.RecordSource=sql
frm1.requery

Also, in the code module of the form, always use the ME keyword to reference form elements, don't use the actual name of the form.

 

[Mabjro]

vbajock,
I have not tried that, but I am going to right now. I will post back my results soon.

Thank you,
mabjro

 

[Mabjro]

vbajock,

After working with your last post, I understand where I have made this confusing. I am having no problem setting the recordsource for frm1. The problem is that once I have done that and it is visible on the screen in normal view, I can't use the value of the field that has focus, in the current record as the record source for another query. This is because, technically frm1 does not exist as a class object. It is just an instance of Form_frmSelectReport. Hopefully I have not lost you beyond return.
mabjro

 

[Mabjro]

The problem is that once I have done that and it is visible on the screen in normal view, I can't use the value of the field that has focus, in the current record as the record source for another "query",

SHOULD READ "form" instead of "query"

 

[cmmrfrds]

What vbajock shows should work and be easier to work with, since the query is expecting a string variable it must be surrouned by quotes.

Something like, but you would need to use the querydef to do this.

SELECT tblInterconnectionDiagram.*, tblInterconnectionDiagram.Description
FROM tblInterconnectionDiagram
WHERE (((tblInterconnectionDiagram.Description)= & chr(34) & Returnmyvar() & chr(34) ));

 

[vbajock]

"The problem is that once I have done that and it is visible on the screen in normal view, I can't use the value of the field that has focus, in the current record as the record source for another "form""

I am really stuck on why this would be so.

It seems that in the Gotfocus event, I could have some code:

Call SetMyvar(me!fldThatHasTheFocusThatINeedValueOf)

I could put a stop statement after that statement, run the code, and when it stops, open the immediate window and type


? GetMyVar()
and see if my variable was loading with the value I think it should. If it is correct, I should be able to use it anywhere. If it is incorrect, then the problem lies within the instantiated form. If it is correct, then the problem resides in the form receiving the value.

 

[Mabjro]

I agree with you VBAJock and that I why this problem is so perplexing. I just figured out what the problem is but I do not know how to fix it. The following link explains what I am trying to do with the form. If you are interested, read through it and give more attention to the part about "non default instance of a form". It turns out that this type of instance can only be referred to by its index number. So my new question is, how can I refer to the non-default instance of a form by its index number?

http://www.microsoft.com/accessdev/articles/vba00572.htm

(should really shed some light on my dilemma.) Thanks
MABJRO

 

[vbajock]

I couldn't read your link, looks like it got chopped.

To refer to forms by number, first make sure you have a refence to DAO 3.6, then:

Function test()
Dim frmObj As Form
Dim dbs As DAO.database
Dim i As Integer

Set dbs = CurrentDb()

i=0

For Each frmObj In Forms

If Forms(i).Name = "frmMain" Then

MsgBox Forms(i).Caption

End If

i = i + 1

Next frmObj

End Function
You should always roll them thru a for each next loop because the index number can change based on what the user is doing. You might want to play with the placement of the i+1 counter - I think the collection starts with Forms(0) but I may be wrong

 

[Mabjro]

Good morning/afternoon/evening VBAJock, (whatever the case may be). Sorry I disappeared yesterday. The !@#$% hit the fan at work yesterday and I was pulled away from my desk. I hope you did not keep on checking back for a response.

The link is

http://www.microsoft.com/accessdev/articles/vba00572.htm

I have worked a little with the index number of member of a class and I believe you are correct, the count starts with 0. Your post helps me to understand how to pin down the forever changing number. I will toy with this on Monday and get back to you. (I should have taken a copy of my database home with me.) Have I nice weekend and thanks for all of your help.

mabjro