How do I Pass Values to Forms

[Alvaro Mendoza]

I tried to pass data to a Form (to open a certain register from a list into a Form). I used DoCmd.OpenForm procedure, and passed the data. but it does not work. Can somebody help me?

DoCmd.OpenForm "Fichas", , , "[c_ficha] = [Forms]![FrmMenu]![SubQryInfoUtilProximasPartidas]![c_ficha]"

And I watched the correct value in c_ficha, when it was called.

[NeoPa]

Alvaro, We can guess that [FrmMenu] is a form, but what are :

1. [SubQryInfoUtilProximasPartidas]?
2. [c_ficha]?

You tell us it does not work, but you give us no clues as to what actually happened. In what way doesn't it work? What do you see when it runs?

You need to provide us with a question if you want us to provide an answer.

[Alvaro Mendoza]

Hi, NeoPA,

First of all, thanks a lot for answering.

[Forms]![FrmMenu]![SubQryInfoUtilProximasPartidas]![c_ficha]

c_ficha is a variable I inherited from the original programer. is the key for the main table, from where I selected the data to fill the form.

the rest was my last trial: is a way to extract the value again from the selected record. In any case, I tried also:

"c_ficha=78"

,

Dim recordID As Integer
recordID = c_ficha
DoCmd.OpenForm "Fichas", , , "[c_ficha] = recordID"

DoCmd.OpenForm "Fichas", , , "name = nameSelected AND lastName = lastNameSelected" 

In every case I watched the values coming into the DoCmd.OpenForm invocation, and they were OK, and the Form I called came out empty.

[NeoPa]

That's a good reply Alvaro, but I still have some points I don't understand. Let me see if I can help with some more numbered questions. Please show the question number by each answer :

1. [Forms]![FrmMenu]![SubQryInfoUtilProximasPartidas]![c_ficha]
   What is [SubQryInfoUtilProximasPartidas] from the expression above?
   SubQry indicates a subquery, but subqueries wouldn't make sense as a control on a form. A subform may, but the naming is strange for a subform. Please explain.
2. What is [c_ficha] from the expression above?
   If [SubQryInfoUtilProximasPartidas] is a subform then it could be a control on that subform, but what type of control and what type of data does it reflect (Numeric? String? Date?)?
3. Is [C_Ficha] a field on the record that [FrmMenu] has as a record source?

With clear answers to these questions I think we can resolve your problem together.

[Alvaro Mendoza]

Hi again, NeoPa.

1. Funny as it is, [SubQryInfoUtilProximasPartidas] is a Form (as I told you before, I inherited this development :-))
2. [c_ficha] Is an Integer, which indicates the Key of the main table (named Fichas) I guess the first developer called it as a short for "code_of_the_ficha", though I wanted to change it to [PeopleID]. But the amount of code to change (and the inestability) would have been major!
3. As in 2., [c_ficha] is the key autonumeric integer value, in Fichas ([Fichas].[c_ficha])

Thanks again.

[NeoPa]

Let me first send you off to look at Referring to Items on a Sub-Form.

When you've read that you can tell me the names of both the SubForm control on [FrmMenu] and the actual form contained within that SubForm.

#2 I need you to answer again. I didn't ask what is [C_Ficha] generally, but what specifically it is with reference to the expression above. I am expecting to see that it is a control of some kind on a form.

#3 I asked wrongly. I meant to say :
Is [C_Ficha] a field on the record that [Fichas] has as a record source?

I assume your answer indicates a Yes.

I don't have all the answers I need for a full answer, but I can give one that at least shows the correct format :

Call DoCmd.OpenForm(FormName:="Fichas", _
                    WhereCondition:="[C_Ficha] = " & Me.???!C_Ficha)

Where ??? is the name of your SubForm control (which may or may not be [SubQryInfoUtilProximasPartidas]).

[Alvaro Mendoza]

Hi, NeoPa,

Sorry for the delay in getting back to you.

FOA, thanks for the clarification, I actually did not pay attention to the fact I was not giving an object.

Second, the problem is now in a "type mismatch" situation:

Call DoCom.OpenForm(FormName:="Fichas", WhereCondition:="[c_ficha] = " & Me.nombre![c_ficha] & ";")

This is the line I came up with. nombre (name in Spanish) is the object from which the event is taken.

As far as I can understand,

Me.nombre

defines the object within the Form. When I added the ![c_ficha], I refered it to the value of the c_ficha field of nombre. Why should this be mismatched?

I used a temporal variable (personaID as integer) to receive the value I was expecting. And it works, but, when I use it to build up the WhereCondition, it stops and warns "type mismatch".

[Alvaro Mendoza]

I tried to work this out, but to no avail. This is the code I wrote:

Private Sub nombre_DblClick(Cancel As Integer)
    Dim personaID As Integer
    Dim personaName As String
    Dim personaApellido As String
    Dim ConditionSelection As String
 
    personaID = Me.c_ficha
    personaName = Me.nombre
    personaApellido = Me.apellido_p
 
    ConditionSelection = "[c_ficha] = " & personaID
    ConditionSelection = ConditionSelection & " AND [nombre] = " & personaName
    ConditionSelection = ConditionSelection & " AND [apellido_p] = " & personaApellido
 
    Call DoCom.OpenForm(FormName:="Fichas", WhereCondition:=ConditionSelection)
    ' & personaID)
End Sub
 

When looking at it in the Watch window, it looks OK, but again... no object error. If I add the Me! instead of Me., I get a no object error. But then I use a Me.[c_ficha] and it show the values right, but the process breaks, for type mis match.

[NeoPa]

Alvaro, you don't answer my questions and I cannot understand what you are posting. I ask questions because I need to in order to understand what you are trying to say. If you don't answer my questions then other comments are not helpful. You can write a book of information, but if you don't answer my questions I am not able to help you. I understand explaining things can be difficult, which is why I ask those clever questions. All you have to do is answer the questions and we can proceed. That should be clear now.

When you introduce new items (such as Me.Nombre - which is certainly an object on the form. Such objects are controls. What type of control I don't know so you need to explain). I cannot help much if you introduce new items without explaining where they fit in. I really can't do much more with post #4 at this time.

For post #5, your code tells me that [Nombre] and [Appellido_P] are both string values. From there I can say that your ConditionSelection should be set up differently for these fields (See Quotes (') and Double-Quotes (") - Where and When to use them). Also, although it does no harm (and provided helpful information in this case), saving these values into variables for the purposes of adding them into this string is unnecessary.

Your procedure could be written as :

Private Sub nombre_DblClick(Cancel As Integer)
    Dim ConditionSelection As String
 
    With Me
        ConditionSelection = "([c_ficha] = " & .C_Ficha & ") AND " & _
                             "([Nombre] = '" & .Nombre & "') AND " & _
                             "([Apellido_P] = '" & .Apellido_P & "')"
    End With
    Call DoCom.OpenForm(FormName:="Fichas", WhereCondition:=ConditionSelection)
    ' & personaID)
End Sub

[Alvaro Mendoza]

Thanks for your patience. Since I'm a rocky in VB, it takes me time to get to what you are asking. I'll try to answer now.

nombre, apellido_p and c_ficha are text boxes in a subform "SubQryInfoUtilProximasPartidas" that is a result of a query in the form of a spread sheet. All the info comes from a single table called "Ficha". There is a form "MenuFrm" which has the sub form as part of it.

I was confused since these Text Boxes have the same name of the fields in the "Fichas" table.

I know that you don't need to add more lines. I did it only to get the useful information you refer to. I did learn out of them.

[NeoPa]

Alvaro, many apologies for the delay in responding to this thread. I keep looking at it and finding myself confused and going around in circles trying to make headway. That is not down to your latest post though, just the accumulation of you not understanding me then me not understanding your responses very well. Let's see what we can do from here.

My first question (Sorry - I'm always asking questions but I don't understand your replies very well so I must do so to continue) is how did the code in post #9 work for you? Did you try it? What happened then?

If that wasn't helpful then I need some more precise information from you. You explain that the main controls are TextBoxes, which is good, but TextBoxes can contain data for various types of fields. Am I right to assume that [Nombre] & [Appellido_P] are string fields whereas [C_Ficha] is numeric?

"SubQryInfoUtilProximasPartidas" is a subform, but from Referring to Items on a Sub-Form we see that subform is a word used for both the control and the contained form. What are the names of your control and form in this case?

Alvaro Mendoza:
I was confused since these Text Boxes have the same name of the fields in the "Fichas" table.

One reason why I always rename my controls with a control type prefix (TextBox -> txt; ListBox -> lst; ComboBox -> cbo; etc). It does create extra work but I find it's worth it. I'm not impressed with MS that the wizards leave a default of the same name.

Sorry again for leaving you hanging for so long.

[Alvaro Mendoza]

Hi, NeoPa!

First of all, you do not need to apology! It's me the one using your time! So thanks and sorry for the unclear content.

I did try your code, but to no avail. The answer was exactly the same as before: it did not bring up the register I was clicking.

Your assumptions are correct: [Nombre] & [Appellido_P] are strings and [c_ficha] is numeric.

The sub-form is called [SubQryInfoUtilProximasPartidas] and I don't know to which control are you referring. I actually changed the name of the control I was clicking to [nombre_Label] (textbox receiving a string) I hope this was what you were asking.

Now, as I told you before, I inherited this system, and I'm doing my best to fix it. I also renamed some of the variables, but I got into trouble because of inconsistencies I couldn't track... So I went back...

So sorry, and thanks again for the patience.

[NeoPa]

OK. Now you need to do two things for me.

First of all find the value of ConditionSelection after it's been set up (After line #8). How to Debug SQL String will help you with that. I also need you to let me know what the values are that you expect the filter string (ConditionSelection) to reflect (So I can see if anything is wrong with the SQL).

Next I need you to read the linked article again and understand the difference between a subform control and the form that is used within that control. I need the names of both and a clear indication of which is which. The article explains it quite clearly I think.

[Alvaro Mendoza]

Dear NeoPa,

I know the value is what was supposed to be, and actually, it only needs the [c_ficha] value, since it is the key for the Fichas table.

I ran the query, and this is the result:
"([c_ficha] = 11) AND ([Nombre] = 'Gabriel') AND ([Apellido_P] = 'Giulietti')"

I think it's the right one.

Now, the form is FormMenu, the container is [SubQryInfoUtilProximasPartidas]. I don't quite grasp the difference, so the only thing I can tell you is that the Query being showed is [QryInfoUtilProximasPartidas]. The control is within the Subform is [nombreTxtBx], which receives strings.

This is the best I could get. I hope it helps.

Thanks again.

Alvaro

[NeoPa]

Alvaro Mendoza:
I know the value is what was supposed to be, and actually, it only needs the [c_ficha] value, since it is the key for the Fichas table.

In that case I expect we can forget about the other details.

What is still unclear is why there is no record in the form ([Fichas]) when it is opened. I assume [Fichas] is a form bound to the [Fichas] table yes?

In that case I don't understand why nothing is showing if a [Fichas] record with a [C_Ficha] value of 11 exists (I assume it does).

Why don't you attach your database for me to have a look at for you. I expect there is something missing from your explanation you simply don't know is important to mention. If I can look at it I may find it. If you think that's worth a try then please follow the instructions in Attach Database (or other work).

PS. We could prbably even change your lines #5 to #7 in post #9 to the following if [C_Ficha] is a unique identifier in its own right :

        ConditionSelection = "([c_ficha] = " & .C_Ficha & ")"

[Alvaro Mendoza]

Dear NeoPa,

This is exactly what I don't understand.

[NeoPa]

I was disappointed to find an ACCDB attached Alvaro. Please read the instructions link again and follow the instructions found there to post a database attachment. I cannot open ACCDB files (as explained in the linked instructions) so I can do nothing yet.

[Alvaro Mendoza]

I know, sorry, I read the instructions, but when I tried to save the DB as 2003, it said it could not. Is there an alternative way to do this? I could send the tables, and then the forms (dividing the DB) will that help?

[NeoPa]

I'm afraid I don't know what's available to you as I've never used 2007 or 2010. However, many members have managed to back-port their databases before. It may be that some part of your database uses 2007 or 2010 specific features. If you make a copy that loses as much as you can while still exhibiting the same problematic behaviour then I would expect a better chance of back-porting to 2003.