Solved calling the value of a field from a main form -> subform into a query (1 Viewer)

[strive4peace]

However! the problem is that the form can't open with criteria that is on the form itself! You must use the LinkMasterFields (m_anstudiu) and LinkChildFields.

Here is the criteria you currently have using m_anstudiu:
Val(Mid([materii.codm],2,1))=Forms!note.Form.m_anstudiu.value (which should be Forms!note!m_anstudiu if you could do it this way, but you can't)

Val(Mid([materii.codm],2,1)) needs to be a calculated field in the recordsource for the subform, and then ON the subform in a control (Visible can be No) -- and whatever the name of that calculated field is should be the control name too. Then that would be LinkChildFields for the subform control

 

[VasiuF]

OK thanks

I've attached an analysis of the SQL in your queries so you can see all the SQL in a Word document.

query for the subform on Note form = _filtru_materii_elev
this reference is wrong:
Forms!note.Form.m_anstudiu.value
Perhaps this should be: Forms!note!m_anstudiu ?

Click in the Navigation Pane and press Ctrl-F to turn on the Find/Filter box at the top if you want to paste the name to find it faster

I cleaned and compiled code but the problem persists
and yes, I still have an application where I only do data exports in excel

 

[strive4peace]

here is your database back.

I modified the _filtru_materii_elev query to remove the reference to the control on the note form

I set these properties for the subform control:
LinkMasterFields = m_matr
LinkChildfields = matr

it populates records for Matricol = 118215

There is another error but I don't know what it says in English!

 

[strive4peace]

what version of Access are you using? I had to reference the Excel Object Library for the code to compile (and you might need a different version) ... and I commented a BUNCH of statements! Look for s4p in the code, short for strive4peace

Go to the code and Compile before testing. If compile isn't available, add a blank line somewhere and compile it first. Then save.
then test

 

[VasiuF]

what version of Access are you using? I had to reference the Excel Object Library for the code to compile (and you might need a different version) ... and I commented a BUNCH of statements! Look for s4p in the code, short for strive4peace

Go to the code and Compile before testing. If compile isn't available, add a blank line somewhere and compile it first. Then save.
then test

office 2007

 

[VasiuF]

office 2007

and 2010

 

[VasiuF]

and 2010

I have 2 computers I work on

 

[strive4peace]

Florin , if you can't open the database, let me know and I'll convert it. You'll need to go to VBA and choose Tools, References, and pick the proper library for Excel BEFORE compiling

if you want to further filter the subform, do something like this:

dim sFilter as string

sFilter = "whatever you calculate it to be"

with  me.subform_controlname.FORM   '_filtru_materii_elev subform -- BUT it can't be called that! You need to rename it
   .filter = sfilter
   .filterOn = true
end with

 

[strive4peace]

Florin , I think Access may have called it
Ctl_filtru_materii_elev_subform
for the code

Names can't start with underscore _

Note, I edited my last message to add .FORM to the end of
with me.subform_controlname.FORM

the reference to the subform control is to the container ... how big it is, where it is, what its source object is

.Form means to go to the form inside it -- where your controls are! And that is the actual form itself.

 

[strive4peace]

Florin, I opened your database fine with Access 2007. It needs to be compiled there though!

 

[VasiuF]

Hi
in visual fox pro I had the following expression:
val (substr (str (codm, 9), 3.1)) which returns my value 2 or 3
specify code value = 202012011,203012011,92012011,93012011
specific that in vfp codm was number but it allowed me to give it 9 fixed length, so it always counts 9 characters.
in the access I did not find the possibility to restore that expression (I cannot give the fixed length of the field)
now I have codm = double
the expressions I tested are:
mid (right (Str ([codm]), 9), 6.1), val (Mid (Str ([codm]), 2.1)) ...
can you help me ?
thank you

 

[strive4peace]

hi Florin,

Unless the database you attached has a different data structure than your working database, codm is a Long Integer in the materii table. But perhaps it should be stored in parts? Data is much easier to combine than it is to break apart.

Instead of taking parts out of your fields, each part that means something should be stored discreetly to begin with.

Please tell us:

• what is codm?
• what are the different parts of codm?
• what does each part mean?

thank you

 

[arnelgp]

your 2nd expression is the equivalent for your vp code.

 

[VasiuF]

hi Florin,

Unless the database you attached has a different data structure than your working database, codm is a Long Integer in the materii table. But perhaps it should be stored in parts? Data is much easier to combine than it is to break apart.

Instead of taking parts out of your fields, each part that means something should be stored discreetly to begin with.

Please tell us:

• what is codm?
• what are the different parts of codm?
• what does each part mean?

thank you

codm is the subject code
202012011 is coding for
20 - specialty
2 - year of study
01 - matter order number
2011 - year of study

the difference appears in the specialty which can be from 1 to 30 (ex. 9,17,24 ...)
matter order number can be: 01.02, ... 30

thank you for your promptness and help

 

[strive4peace]

thanks and you're welcome, Florin

codm needs to be 4 different fields in the table structure. That will make things immensely easier!

Now, you might think to yourself: but we use that combination as an identifier. Fine. Give users an unbound control to type it in all together if they wish, and then separate it out on the AfterUpdate event

 

[strive4peace]

you can put a calculated control on the form that combines it. You'd still need an unbound control to enter a new one because you can't change the value of a calculated control. Or you could use the unbound control to combine also, by using code to put the value in on the form Current event, and also the control AfterUpdate events for each part. One unbound control would only work if the form shows just one record at a time. For multiple records, you'd need a calculated control to show the combination.

 

[VasiuF]

you can put a calculated control on the form that combines it. You'd still need an unbound control to enter a new one because you can't change the value of a calculated control. Or you could use the unbound control to combine also, by using code to put the value in on the form Current event, and also the control AfterUpdate events for each part. One unbound control would only work if the form shows just one record at a time. For multiple records, you'd need a calculated control to show the combination.

I did not specify, the expression I want to use in a query as a condition of filtering records in the table, not in a form
I have to paste existing data and then process it

 

[strive4peace]

hi Florin,

Tables are for storing data
Forms are for entering and editing data

You could make codm a calculated field in your table, so it could appear all together but you still need the 4 separate fields. Everything will be so much easier once you do that.

codm would be stored as Short Text (string) with a Size of 9, and would be a Calculated field. It would not be stored as a number even though its data is numeric. The general rule of thumb is that if you won't add it or do math with it, then it should be text.

 

[strive4peace]

> "paste existing data and then process it"

just because you get the data that way doesn't mean the database should keep it that way. Do you get the data as a text file? As an Excel spreadsheet? How does it come?

 

[strive4peace]

I've got to go for now. I'll check back later. I hope to see your restructured table