Use calculate age to write other fields automatically
in [Forms]
|
Prev: cancel form unload
Next: MDE in A2003, A2007
From: KenSheridan via AccessMonster.com on 5 May 2010 14:08 Diego: Apologies for the delay in replying. I've been visiting my new granddaughter who was born this morning. Una bambina bella! I think the problem is that when you assign a value to a control with code the control's AfterUpdate event does not in fact execute. It only does so if the user enters data manually. This explains why it works when you type the date in, but not when its assigned by the InputCalendario function. What you can try, however, is calling the functions to insert the values into the controls from within the InputCalendario function by adding lines at the end like this: InputCalendario = DataOut ' add these 3 lines On Error Resume Next Forms!YourFormname!Categoria = GetCategoria(DataOut) Forms!YourFormname!Descrizione = GetDescrizione (DataOut) End Function The On Error Resume Next line is there in case the function is called while the form which contains the Categoria and Descrizione controls is not open, in which case the error will be ignored. Ken Sheridan Stafford, England Diego wrote: >Hi Ken again >the problem is not in the format date, but how the date is selected. >If i write by hand the date it works fine, but if i select from InputCalendar >it does not works. >It is like if i write nothing in the date . Strange, because the date in the >table is write correctly. > >No idea how to solve this issue. >Below the InputCalendario Function > >Sorry by that >Let me know >Diego > >Public Function InputCalendario(Data As String, Titolo) > >'********************************************************************************************* >'* Funzione che visualizza il calendario e restituisce la data scelta >'* Argomenti ricevuti: - Data; se = 0, viene proposta la data del giorno >'* - Titolo della maschera 'Calendario' >'* Valore restituito: - Data scelta dall'utente; se è stato premuto il tasto >di uscita: >'* - Data ricevuta in input, se > 0 >'* - Valore Null, se = 0 >'* Non viene reso 0, perchè Access lo interpreta come >30/12/1899 >'* (non 31/12/1899 perché per Microsoft il 1900 era >bisestile) >'* - La posizione in cui viene visualizzato il calendario viene calcolata in >base >'* alla posizione del mouse >'********************************************************************************************* > > Dim stDocName As String, frm As Form > Dim Posizione As POINTAPI > Dim PuntoX As Integer, PuntoY As Integer > Dim SpostamentoX As Integer, SpostamentoY As Integer, Larghezza As >Integer, Altezza As Integer > > ' Calcola i valori per la gestione dello schermo > ValoriSchermo > > ' Determina la posizione del mouse > ' La funzione GetCursorPos restituisce in una variabile di tipo definito >dall'utente denominata POINTAPI le coordinate x,y relative all'angolo >superiore a sinistra dello schermo. > GetCursorPos Posizione > > 'Ricavo la posizione x e y > PuntoX = Posizione.X 'coordinata del punto x > PuntoY = Posizione.Y 'coordinata del punto y > > ' Calcolo la posizione della maschera > ' - Posizione X = Pos.-X- del mouse + SpostamentoX > ' se, sommata alla larghezza della maschera, supera il lato destro >dello schermo: > ' Posizione X = Pos.-X- del mouse - SpostamentoX - larghezza maschera > ' Il calcolo per -Y- è analogo > ' Dimensioni maschera: dimensioni in cm * 567 (1 cm = 567 Twips) > > Larghezza = 4 * 567 ' Larghezza maschera calendario > Altezza = 6 * 567 > SpostamentoX = 800 > SpostamentoY = 0 > > PosDx = (PuntoX) * TwipsPerPixelX > PosDx = IIf(((PosDx + SpostamentoX + Larghezza) > MaxTwipsX), (PosDx - >SpostamentoX - Larghezza), (PosDx + SpostamentoX)) > PosVr = (PuntoY) * TwipsPerPixelY > PosVr = IIf(((PosVr + SpostamentoY + Altezza) > MaxTwipsY), (PosVr - >SpostamentoY - Altezza), (PosVr + SpostamentoY)) > > TitCal = Titolo > stDocName = "Calendario" > DataIn = Data > > 'Apri in modale la maschera > DoCmd.OpenForm stDocName, , , , , acDialog > > InputCalendario = DataOut > >End Function > >>Hi Ken >>the problem is related to format date. >[quoted text clipped - 16 lines] >>>>>>>>>>> Best regards >>>>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Mike Painter on 6 May 2010 01:28 Diego via AccessMonster.com wrote: > Hi Ken > also your suggestion does not works. The Categoria and Descrizione > are empty. It seem that the calculation of strCriteria does not works. > My date in the table has medium format (21-May-10) . This can be a > problem ? I use this format to avoid difference from Italy and USA > date format. > Sure this method is better. > Let me know > Diego > To repeat what I said a few posts ago. THIS IS A RELATIONAL DATABASE. Place the age calculation in a query, relate the Categoria and Descrizione table in that query. Use the query for all forms and reports. No "lookup" is needed.
From: Diego via AccessMonster.com on 6 May 2010 04:03 Great !!! It is wonderful !!!! Welcome at your new granddaughter and greetings from Italy to you and your family. Thank you for all your suggestions Ciao from Italy Diego Thank you for KenSheridan wrote: >Diego: > >Apologies for the delay in replying. I've been visiting my new granddaughter >who was born this morning. Una bambina bella! > >I think the problem is that when you assign a value to a control with code >the control's AfterUpdate event does not in fact execute. It only does so if >the user enters data manually. This explains why it works when you type the >date in, but not when its assigned by the InputCalendario function. > >What you can try, however, is calling the functions to insert the values into >the controls from within the InputCalendario function by adding lines at the >end like this: > > InputCalendario = DataOut > > ' add these 3 lines > On Error Resume Next > Forms!YourFormname!Categoria = GetCategoria(DataOut) > Forms!YourFormname!Descrizione = GetDescrizione (DataOut) > >End Function > >The On Error Resume Next line is there in case the function is called while >the form which contains the Categoria and Descrizione controls is not open, >in which case the error will be ignored. > >Ken Sheridan >Stafford, England > >>Hi Ken again >>the problem is not in the format date, but how the date is selected. >[quoted text clipped - 84 lines] >>>>>>>>>>>> Best regards >>>>>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Diego via AccessMonster.com on 10 May 2010 11:15 Hi Ken since your suggestion works fine i have a new question How to do the same thing in a query ? I refer to your last suggestion ------------------------------------------------------------------------------ --------------------------- >strCriteria = "Anni = " & _ > DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > >Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria) >Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria) ------------------------------------------------------------------------------ -------------------------- Ciao Diego KenSheridan wrote: >Diego: > >The #name error is probably because I'd put the 'c' and 'r' the wrong way >round in the function name. It should have been: > >Function GetDescrizione(dtmData_Nascita As Date) As String > > Dim intAnni As Integer > > intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > > If intAnni >= 12 And intAnni <= 14 Then > GetDescrizione = "Pulcini " & intAnni - 11 & "_A" > Else > GetDescrizione = "Not within age range" > End If > >End Function > >Apart from that I see no reason why both should not work. Data_Nascita >should be a bound control containing the date of birth as a date/time data >type, and the code should be in its AfterUpdate event procedure. I still >don't see any reason why the Categoria and Descrizione values need be stored >in the table, however, as they can be computed from the Data_Nascita value at >any time, so are redundant. > >While the values can be obtained with functions in this way, its not really a >good way of doing it as it requires data to be represented in the code. In a >relational database data should only be stored in tables. A better way would >be to have another table, Anni_Categoria say, with columns Anni, Descrizione >and Categoria, with the following rows: > >12 Pulcini 1_A P_1 >13 Pulcini 2_A P_2 >14 Pulcini 3_A P_3 > >You can then look up the values from that table in the Data_Nascita control's >AfterUpdate event procedure: > >Dim strCriteria As String >Dim dtmData_Nascita As Date > >dtmData_Nascita = Me.Data_Nascita > >strCriteria = "Anni = " & _ > DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > >Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria) >Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria) > >By having the data in the Anni_Categoria table it can be updated at any time >simply by editing records in the table, so if further categories are added or >the basis for determining the categories changes the is no need to amend any >code. > >Ken Sheridan >Stafford, England > >>Hi Ken >>i tried >[quoted text clipped - 12 lines] >>>>>>>> Best regards >>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: KenSheridan via AccessMonster.com on 10 May 2010 16:29
Diego: If you want to update the Descrizione and Categoria field's values you'd use an update query along these lines: UPDATE YourTable SET Descrizione = DLookup("Descrizione","Anni_Descrizione", "Anni = " & DateDiff("yyyy", Data_Nascita, Date()) - IIf(Format( Data_Nascita, "mmdd") > Format(VBA.Date, "mmdd"), 1, 0)), Categoria = DLookup("Categoria","Anni_Descrizione","Anni = " & DateDiff("yyyy", Data_Nascita, Date()) - IIf(Format( Data_Nascita, "mmdd") > Format(VBA.Date, "mmdd"), 1, 0)); To return the values in computed columns use the same expressions. Ken Sheridan Stafford, England Diego wrote: >Hi Ken >since your suggestion works fine i have a new question >How to do the same thing in a query ? > >I refer to your last suggestion > >------------------------------------------------------------------------------ >--------------------------- >>strCriteria = "Anni = " & _ >> DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ >[quoted text clipped - 3 lines] >>Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria) >>Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria) > >------------------------------------------------------------------------------ >-------------------------- > >Ciao >Diego > >>Diego: >> >[quoted text clipped - 63 lines] >>>>>>>>> Best regards >>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1 |