case a not associated subform and the code for copy and paste from it to his fatherform

[MF_PA]

good morning, I have prepared a parent form called frm_Disallineamenti which contains various unassociated fields, Among the main of these fields, there are Anno1, CIUProv1 and ISTATProv1. In this mask there are many bottom/options (values from 01 to 26) and contains a child mask called SM_Disallineamenti whith the fields Year, IstatProv, CiuPro, CampoSospetto and ValoreRiscontrato. This subform is not associated with anything else, and depending on the value of bottom/option it links to my queries

Private Sub CaricaSlittamenti(Scelta As Integer)



Select Case Scelta
'it works with my option/bottom
Case 1 ' OpAA


Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inAA"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA AA"

Case 2 ' OpBB
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inBB"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA BB"

Case 3 ' OpBE
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inBE"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA BE"

Case 4 ' OpDA
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inDA"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA DA"

Case 5 ' OpDB
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inDB"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA DB"

' === GRUPPO 2 ===
Case 6 ' OpIA
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inIA"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA IA"

Case 7 ' OpIB
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inIB"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA IB"

Case 8 ' OpIC
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inIC"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA IC"

Case 9 ' OpID
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inID"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA ID"

Case 10 ' OpIE
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inIE"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA IE"

Case 11 ' OpIF
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inIF"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA IF"

Case 12 ' OpRA
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRA"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RA"

Case 13 ' OpRB
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRB"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RB"

Case 14 ' OpRC
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRC"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RC"

Case 15 ' OpRD
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRD"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RD"

Case 16 ' OpRE
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRE"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RE"

Case 17 ' OpRF
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inRF"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA RF"

Case 18 ' OpVC
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVC"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VC"

Case 19 ' OpVD
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVD"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VD"

Case 20 ' OpVE
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVE"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VE"

Case 21 ' OpVF
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVF"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VF"

Case 22 ' OpVG
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVG"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VG"

Case 23 ' OpVH
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVH"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VH"

Case 24 ' OpVU (se lo usi in futuro)
Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_inVU"
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA VU"

Case Else
Me.SM_SlittamentiSospetti.SourceObject = ""
Me.lblTitolo.Caption = "Seleziona una ricerca"
End Select


End Sub

The child form is displayed in sheet mode. This allows me to avoid having to create 26 child masks. However, when I click on the row of my subform, I can't import the values of Year, CIUProv and ISTATProv into the txbox of my parent mask (Anno1, CIUProv 1e ISTATProv1).

Private Sub Form_Click()
Forms!frm_Disallineamenti.ISTATProv1 = Me.IstatProv
Forms!frm_Disallineamenti.CIUProv1 = Me.CIUProv
Forms!frm_Disallineamenti.ISTATProv1 = Me.IstatProv

end sub
Thanks for your answers

[Welshgasman]

Just use Me.Parent.ISTATProv1 = Me.IstatProv

I would not use that numeric for the Case as well.

As you have a good naming convention, you could do all of those with three lines.

strCelta = Right(Me.Celta,2) ' The text value, not the numeric value

Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_in" & StrCelta
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA " & StrCelta

Where strCelta is AA, BB, BE etc.

[June7]

You are using query objects as subform SourceObject. There is no form in SM_Disallineamenti so there is no Click event available.

I wonder why you have 26 query objects. Why use a query as SourceObject instead of a form and apply filter to form? Zero query objects.

And instead of VBA setting label caption, use a textbox with expression.

Why are you using UNBOUND form? Why need to display data from SM_Disallineamenti onto main form?

BTW, should use CODE tags instead of QUOTE when posting code.

[MF_PA]

Just use Me.Parent.ISTATProv1 = Me.IstatProv

I would not use that numeric for the Case as well.

As you have a good naming convention, you could do all of those with three lines.

strCelta = Right(Me.Celta,2) ' The text value, not the numeric value

Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_in" & StrCelta
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA " & StrCelta

Where strCelta is AA, BB, BE etc.

good idea, you mean:

Select Case Scelta
'it works with my option/bottom

dim strScelta as string




Case 1 ' OpAA
strScelta =”AA”

Case 2 ' OpBB
strScelta =”BB”

Case 3 ' OpBE
strScelta =”BE”

Case 4 ' OpDA
strScelta =”DA"

Case 5 ' OpDB
strScelta =”DB"

' === GRUPPO 2 ===
Case 6 ' OpIA
strScelta =”IA"

Case 7 ' OpIB
strScelta =”IB"

Case 8 ' OpIC
strScelta =”IC"

Case 9 ' OpID
strScelta =”ID"

Case 10 ' OpIE
strScelta =”IE"

Case 11 ' OpIF
strScelta =”IF"

Case 12 ' OpRA
strScelta =”RA"

Case 13 ' OpRB
strScelta =”RB"

Case 14 ' OpRC
strScelta =”RC"

Case 15 ' OpRD
strScelta =”RD"

Case 16 ' OpRE
strScelta =”RE"

Case 17 ' OpRF
strScelta =”RF"

Case 18 ' OpVC
strScelta =”VC"

Case 19 ' OpVD
strScelta =”VD"

Case 20 ' OpVE
strScelta =”VE"

Case 21 ' OpVF
strScelta =”VF"

Case 22 ' OpVG
strScelta =”VG"

Case 23 ' OpVH
strScelta =”VH"

Case 24 ' OpVU (OPTIONAL)
strScelta =”VU"

Case Else
Me.SM_SlittamentiSospetti.SourceObject = ""
Me.lblTitolo.Caption = "Seleziona una ricerca"
End Select

Me.SM_SlittamentiSospetti.SourceObject = "Query.QSlittamentiSospetti_in" & StrScelta
Me.lblTitolo.Caption = "RICERCA SLITTAMENTI SOSPETTI NELLA TABELLA " & StrScelta


End Sub

so it works. But , also using in my

Private Sub Form_Current() ' SM_SlittamentiSospetti
Me.Parent.Anno1 = Me.AnnoMe.Parent.ISTATProv1 = Me.IstatProv
Me.Parent.CIUProv1 = Me.CIUProv
end sub

I had no results in my Anno1,ISTATProv1 and CIUProv1

[Welshgasman]

No, you still have all those case statements.
Use the text value and not it's numeric equivalent.
Upload enough of the dB to see the issue, as I am going on what I think you have told us, and June7 seems to know the dB,but I do not?

[June7]

No, I don't know the db, just the posted code.

Again, using query object as SourceObject so there are no form events available. What is posted makes no sense.

To provide db, follow instructions at bottom of my post.

[Welshgasman]

So the O/P should be setting recordsource and not sourceobject?
I was going on the subform/mainform descriptions.

Again, what is the difference between the queries? Is it just that two character value?

[MF_PA]

Upload enough of the dB to see the issue, as I am going on what I think you have told us, and June7 seems to know the dB,but I do not?

look into atachement . The dark part and its controls contain the 26 buttons (therefore my cases) and the sub-mask where the suspicious cases and the primary key (Year, IstatProv, CiuProv) are displayed, showing for example a postal code that ended up in the "Street" column or in the telephone area code column. I mean, use a click or a double click on a suspicious record to display in the controls contained in The red box, the main information of the company (for exampler read the telephone number to make contacts and ask to correct incorrect data)

[June7]

Posted image does not change my comments.

A compound primary key? A real pain - I have resorted to only once.

Conventional approach is to use UNBOUND controls on main form for input of filter criteria and apply to subform.

You appear to be trying to mimic a split form.

[MF_PA]

There are two queries at the moment, the others I will have to prepare after being sure that my mask works correctly. here is the code of my QSlittamentiSospetti_inAA

SELECT 
    AA.Anno,
    AA.IstatProv,
    AA.CIUProv,
    "ViaSL" AS CampoSospetto,
    Left(Trim([AA].[ViaSL]),5) AS ValoreRiscontrato
FROM 
    AA
    INNER JOIN [q_CAP_principali] 
        ON Left(Trim([AA].[ViaSL]),2) = Left(Trim([q_CAP_principali].[Cap]),2)
WHERE Len(Trim([AA].[ViaSL])) = 5
 
UNION ALL
 
SELECT 
    AA.Anno,
    AA.IstatProv,
    AA.CIUProv,
    "Via" AS CampoSospetto,
    Left(Trim([AA].[Via]),5) AS ValoreRiscontrato
FROM 
    AA
    INNER JOIN [q_CAP_principali] 
        ON Left(Trim([AA].[Via]),2) = Left(Trim([q_CAP_principali].[Cap]),2)
WHERE Len(Trim([AA].[Via])) = 5
 
UNION ALL
 
SELECT 
    AA.Anno,
    AA.IstatProv,
    AA.CIUProv,
    "PrefTel" AS CampoSospetto,
    Left(Trim([AA].[PrefTel]),5) AS ValoreRiscontrato
FROM 
    AA
    INNER JOIN [q_CAP_principali] 
        ON Left(Trim([AA].[PrefTel]),2) = Left(Trim([q_CAP_principali].[Cap]),2)
WHERE Len(Trim([AA].[PrefTel])) = 5
 
UNION ALL
 
SELECT 
    AA.Anno,
    AA.IstatProv,
    AA.CIUProv,
    "PrefTelSL" AS CampoSospetto,
    Left(Trim([AA].[PrefTelSL]),5) AS ValoreRiscontrato
FROM 
    AA
    INNER JOIN [q_CAP_principali] 
        ON Left(Trim([AA].[PrefTelSL]),2) = Left(Trim([q_CAP_principali].[Cap]),2)
WHERE Len(Trim([AA].[PrefTelSL])) = 5
 
UNION ALL SELECT 
    AA.Anno,
    AA.IstatProv,
    AA.CIUProv,
    "MesiAttivitaAnno" AS CampoSospetto,
    AA.Mesi_di_attivita_anno AS ValoreRiscontrato
FROM AA
WHERE AA.Mesi_di_attivita_anno Not In 
      ("00","01","02","03","04","05","06","07","08","09","10","11","12");


 and again here is the code of the second query (QSlittamentiSospetti_inBB)
SELECT 
   BB.Anno,
   BB.IstatProv,
    BB.CIUProv,
    "Via" AS CampoSospetto,
    Left(Trim([BB].[Via]),5) AS ValoreRiscontrato
FROM 
    BB
    INNER JOIN [q_CAP_principali] 
        ON Left(Trim([BB].[Via]),2) = Left(Trim([q_CAP_principali].[Cap]),2)
WHERE Len(Trim([BB].[Via])) = 5
 
UNION ALL SELECT 
    BB.Anno,
    BB.IstatProv,
    BB.CIUProv,
    "UnitMis" AS CampoSospetto,
    BB.UnitMis AS ValoreRiscontrato
FROM 
    BB
WHERE 
    BB.UnitMis IN ("3","4","5","6","7","8","9");

[MF_PA]

it' not my database, i've just to work on it and resolve the errors. The database in Access is divided into two parts, one acts as the software and the other the pure database is queried by this software. I can't change anything about the database settings, but I can add forms to the software that make it easier for me to look for errors in the database.

[June7]

Resorting to UNION query for core functionality is indicator of non-normalized data structure, as is multiple tables with same structure.

Still have not addressed my comments about use of query as SourceObject of subform container.

Even if you don't change structure, at the least, use a form and set its RecordSource property.

Again, suggest you provide db for analysis if you want more assistance.

[MF_PA]

Resorting to UNION query for core functionality is indicator of non-normalized data structure, as is multiple tables with same structure.

Still have not addressed my comments about use of query as SourceObject of subform container.

Even if you don't change structure, at the least, use a form and set its RecordSource property.

Again, suggest you provide db for analysis if you want more assistance.

I send you an empty database with all its tables. I filled the tables AA, AB,BB, with made-up data, but in some of them I added errors like the zip code in the VIA column.
I've forgot. It's an MDB and not ACCDB, but I have to work on this type of file

[Welshgasman]

I have no idea as to what you keep addressing as a mask?
A mask to me is something that hides something?

Why not just have a combo for all those option buttons? Or is that just what you are stuck with?

All I see is tables?

[June7]

We need the frontend as well.

This is the most bizarre relationships setup I have ever seen. 3 and 4 fields for compound keys!!!!

What purpose does this db serve? Why not some meaningful table names?

I see that tables are not identical in structure but there is enough commonality that a form could work.