form recordset mess up the form

[diegomarino]

Hi,
i have a calendar table related with a partecipants table, it is a many to many relation this is the scheme


------------------------------
CALENDAR
IDCalendar, Title, date .....

PARTECIPANTS
CalendarID, PArtecipantID, Qualification
------------
so, every row of calendar has usually many (2-4) related partecipants rows, cause every calendar event can be shared by many persons.

since i can't put a subform in a continuos form (yeah, the head/footer solution doesn't fit my needs) i'm make a routine to give values to unbound textbox in my calendar continuos form, this is the code

   Set rsRecords = Me.Recordset   Set rs = db.OpenRecordset("SELECT Partecipanti.CalendarioID, Partecipanti.PartecipanteID, Partecipanti.Qualifica, Partecipanti.ID, Partecipanti.Mail, Candidati.nomecognome " & vbCrLf & _
                             "FROM Candidati INNER JOIN Partecipanti ON Candidati.IDcandidato = Partecipanti.PartecipanteId;")


      rsRecords.MoveFirst
      Do While Not rsRecords.EOF
      Debug.Print (rsRecords.RecordCount)
         rs.FindFirst ("CalendarioID =" & Me.IDCalendario & " AND Qualifica = 'candidato'")
         Debug.Print (Me.IDCalendario)
         Me.Candidato = rs![NomeCognome]
         Debug.Print (rs![NomeCognome])
      rsRecords.MoveNext
   Loop

if i set rsRecords = me.recordset and i look the loop in debug print, it loops actually throught every record my form shows, but at the end i see this



the form is broken


if i set rsRecords = me.recordsetClone the form is ok, but the loop stucks only on the first record.

i cannot figure it out what happens, since i get no errors


ps the recordsource of the form is a query

"SELECT Calendario.idcalendario, Calendario.Completato, Calendario.Priorità, Calendario.[Follow Up], Calendario.Manager, Calendario.Tipo1, Calendario.Titolo1, Calendario.[Data inizio], Calendario.Inizio, Calendario.Fine, Calendario.TrattativaID, Calendario.CittàID, Calendario.Commento, Calendario.Indirizzo, Aziende.Azienda, Comuni.Comune, Candidati.NomeCognome, Partecipanti.qualifica " & vbCrLf & _                     "FROM Candidati AS Candidati_2 RIGHT JOIN (Partecipanti AS Partecipanti_2 RIGHT JOIN (Candidati AS Candidati_1 RIGHT JOIN (Partecipanti AS Partecipanti_1 RIGHT JOIN (Candidati RIGHT JOIN (Partecipanti RIGHT JOIN (Comuni RIGHT JOIN ((Aziende RIGHT JOIN Clienti ON Aziende.IDazienda = Clienti.AziendaID) RIGHT JOIN (Trattative RIGHT JOIN Calendario ON Trattative.IDtrattativa = Calendario.TrattativaID) ON Clienti.IDCliente = Trattative.ClienteID) ON Comuni.IDComune = Calendario.CittàID) ON Partecipanti.CalendarioID = Calendario.IDCalendario) ON Candidati.IDcandidato = Partecipanti.PartecipanteID) ON Partecipanti_1.CalendarioID = Calendario.IDCalendario) ON Candidati_1.IDcandidato = Partecipanti_1.PartecipanteID) ON Partecipanti_2.CalendarioID = Calendario.IDCalendario) ON Candidati_2.IDcandidato = Partecipanti_2.PartecipanteID " & vbCrLf & _
                     "WHERE (((Partecipanti.qualifica)=""Head Hunter"")) " & vbCrLf & _
                     "ORDER BY Calendario.[Data inizio], Calendario.Inizio;"

[Bob Fitz]

since i can't put a subform in a continuos form ...........

But you could put a subform on a form in Continuous view.

I don't understand what you are trying to do in your code but if you are trying edit the data in rsRecords then you will need the line:
rsRecords.Edit
at the start of each loop and you will need the line:
rsRecords.Update
before the line:
rsRecordset.MoveNext

[moke123]

i'm make a routine to give values to unbound textbox in my calendar continuos form

not sure you can do that in a continuous form.

post a copy of your db with a few dummy records.

[diegomarino]

@bob fitz yes, but the subform cannot be in the detail of continuos form, only in foot or header

here a copy of database

as you can see in the relation image (just to show, i have not that relation actually), every row of "calendario" is related to many rows of "partecipanti", cause every calendar has many attendees.

i want to make some unbound textbox in calendar list ("lista calendario") that i fill with records in "partecipanti". So i loop through the query made with "partecipanti" and "candidati", and when the record matches the "calendarioID=IDcalendario" (the key of calendar table) and the "qualifica" (the function of the attendee) i want, it fills the related texbox.

in this case i just made a "Candidato" textbox (applicant), so the recordset should loop through the "Partecipanti" query (applicants) and sit on the record that has the "Calendarioid" (the foreign key) of the specific calendar row and has the "qualifica" = "Candidato".

this is the scheme of relationship

[Bob Fitz]

Earlier message was made in error.

[diegomarino]

there was an error on load a form not existing in start up

[Gicu]

Hi Diego,
Please look at it now, should work OK (I'll let you fix the #Type error showing on the calculated control in the new record line ). You were use rsRecords.MoveNext but you were getting the IDCalendario from Me (the form not the recordset) which was not advancing therefore the first record always.

Cheers,
Vlad

[ssanfu]

I would suggest you stop and fix other issues before moving on.

Remove spaces from ALL object names.
Remove special characters from Field names. (at least 3 fields with "/" in the name)
Having "ID" as the PK field in all 3 tables is just wrong!

IMHO, having 83 fields in 1 table ("Candidati") suggests that the table should be looked at and split into more tables.

There is an error in the sub "OggiFilt_Click"

Private Sub OggiFilt_Click()
   Me.CompletatoFilt = No         '<<--- You cannot use No. You must use FALSE
   Me!DiegoFilt = True
   Me!DataInizioFilt = Date
   Me!DataFineFilt = Date
   Me!CandidatoFilt = ""
   Me!TitoloFilt = ""
   BuildFiltStr
End Sub

Also, I believe the table linking is not correct. There is not a PK field linking to a FK field.

[diegomarino]

what a jackass...
however, i tried with debug print and the problem is still there....


i checked and idcalendario is different for every record, i changed oggi_filt to set the date of monday, in which there are 9 events.

[diegomarino]

thanks nice suggestion.

ok i'lll switch to false, even if it works with "no" too
I know my fileds are bad but i made it a year ago that i was very ignorant. I have to change it but i'm scared cause i have one ms access application and one powerapps application...changing names is a bit painful i think
all "ID" field are system generated, i do not use them, i use my personalized ID, like "IDcalendario", "IDCandidato" etcetc

speaking of splitting Candidati Table, do you think it will boost performances? right now even forms based on candidati are very fast.... maybe you suggest to make for example a "retribution" table in which io put every retribution and contractual information in "Candidati" table?

I would suggest you stop and fix other issues before moving on.

Remove spaces from ALL object names.
Remove special characters from Field names. (at least 3 fields with "/" in the name)
Having "ID" as the PK field in all 3 tables is just wrong!

IMHO, having 83 fields in 1 table ("Candidati") suggests that the table should be looked at and split into more tables.

There is an error in the sub "OggiFilt_Click"

Private Sub OggiFilt_Click()
   Me.CompletatoFilt = No         '<<--- You cannot use No. You must use FALSE
   Me!DiegoFilt = True
   Me!DataInizioFilt = Date
   Me!DataFineFilt = Date
   Me!CandidatoFilt = ""
   Me!TitoloFilt = ""
   BuildFiltStr
End Sub

Also, I believe the table linking is not correct. There is not a PK field linking to a FK field.

[Gicu]

Diego,
You have the same problem on the Debug.Print (Me.IDCalendario) should be rsRecords!IDCalendario (and all records in Candidati have Martin for NomeCognome). But reading your initial post again I don't think you can do what you want as the unbound controls on a continuous form will display the same value for all records. In the loop you are retrieving the correct values but you are always assigning them to Me.Candidato for the first record.
One way to do it would be to use dLookups for the various unbound controls or concatenate them using a concatenation functions. Please have a look at the updated file where I use theDBguy's SimpleCSV function to do it. If you apply the daily filter on the open event of the form it should be very fast.

Cheers,
Vlad

[diegomarino]

thanks, i feared it was not possible. However i tried the dlookup but it was i bit slow, this solution looks faster.
maybe i'll think something to make it faster, but it's ok


Actually, i was thinking a stupid idea. Making a table from the query i use for calendar recordsource, adding Applicants, clients and headhunters in other fields.
This should work and should be the fastest way

[Gicu]

It probably will be a bit faster but the constant emptying and populating of the local table will bloat the front-end so it will need to be compacted frequently plus the data might not always reflect the "live" data if other user(s) are modifying existing records. For filtered recordsets with few records the SimpleCSV should be fast enough I think.

Cheers,

[moke123]

The first picture in Post#9 you set db = currentdb but there is no indication you dimmed it. Do you have option explicit declared in every module?

[diegomarino]

yes, do not look too much at that code, it was messed up many times

The first picture in Post#9 you set db = currentdb but there is no indication you dimmed it. Do you have option explicit declared in every module?