Results 1 to 12 of 12

Thread: Help Pls

  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face Unanswered: Help Pls

    I have problem using date function:

    I have main form with subform. In “mainfrom” I have field name “BirthDate” and in subfrom I have one combo box “cboVisitType” and “txtScheduleDate”

    This is what I’m trying to accomplish: Let say user input "BirthDate" in main form. And then in subfrom they pick visit type from “cboVisitType” if user pick “2WeeksVisit” I want to display “BirthDate + 2Weeks” in “txtScheduleDate”

    If the user pick 4 Weeks then it should be “BirthDate + 4 Weeks”
    If the user pick 2 Months then it should be “BirthDate + 2 Months”

    Thanks in advance
    Skharva

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    Re: Help Pls

    Originally posted by Skharva
    I have problem using date function:

    I have main form with subform. In “mainfrom” I have field name “BirthDate” and in subfrom I have one combo box “cboVisitType” and “txtScheduleDate”

    This is what I’m trying to accomplish: Let say user input "BirthDate" in main form. And then in subfrom they pick visit type from “cboVisitType” if user pick “2WeeksVisit” I want to display “BirthDate + 2Weeks” in “txtScheduleDate”

    If the user pick 4 Weeks then it should be “BirthDate + 4 Weeks”
    If the user pick 2 Months then it should be “BirthDate + 2 Months”

    Thanks in advance
    If you are storing both month and week as a variable for the amount of time you need to add you will have to add some additional code

    An example
    dim interval as string
    dim number as integer

    number = left(cboVisitType.value,1) 'this is assuming no values are more than to character integers
    interval = left(right(cboVisitType.value,len(cboVisitType.val ue)-1),1) 'this pulls the value of teh first character to the right of the integer that determines the number

    if interval = "w" then interval = "ww" 'if weeks is selected ww is a way to add weeks, w only adds workday

    txtscheduledate = dateadd(inerval, number, birthdate)


    **Note**
    If you are pulling values from a subform on a form there may need to be additional references made to the controls.

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face Re: Help Pls

    Originally posted by mjweyland
    If you are storing both month and week as a variable for the amount of time you need to add you will have to add some additional code

    An example
    dim interval as string
    dim number as integer

    number = left(cboVisitType.value,1) 'this is assuming no values are more than to character integers
    interval = left(right(cboVisitType.value,len(cboVisitType.val ue)-1),1) 'this pulls the value of teh first character to the right of the integer that determines the number

    if interval = "w" then interval = "ww" 'if weeks is selected ww is a way to add weeks, w only adds workday

    txtscheduledate = dateadd(inerval, number, birthdate)


    **Note**
    If you are pulling values from a subform on a form there may need to be additional references made to the controls.


    I have use follwing codes but still there is problem which i can't figure it out

    1. Mainform name = frmMain
    2. SubformName =frmSub
    In the afterupdate event of cboVisitType put

    Select Case cboVisit.Value
    Case "1WeeksVisit”
    Forms!subfrmInfants!frmVisit!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    Case "2WeeksVisit”
    Forms!subfrmInfants!frmVisit!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    Case “3WeeksVisit”
    Forms!subfrmInfants!frmVisit!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    Case "4WeeksVisit”
    Forms!subfrmInfants!frmVisit!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    End Select

    it gives me error saying Method or Data memeber not found for (Me.txtBirthDate) and then when i change that to (Me.txtScheduleDate) it gives me error saying "SubfrmInfants" can't be found



    Thanks is advance
    Skharva

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Re: Help Pls

    Originally posted by Skharva
    I have problem using date function:

    I have main form with subform. In “mainfrom” I have field name “BirthDate” and in subfrom I have one combo box “cboVisitType” and “txtScheduleDate”

    This is what I’m trying to accomplish: Let say user input "BirthDate" in main form. And then in subfrom they pick visit type from “cboVisitType” if user pick “2WeeksVisit” I want to display “BirthDate + 2Weeks” in “txtScheduleDate”

    If the user pick 4 Weeks then it should be “BirthDate + 4 Weeks”
    If the user pick 2 Months then it should be “BirthDate + 2 Months”

    Thanks in advance
    If you are storing both month and week as a variable for the amount of time you need to add you will have to add some additional code

    An example
    dim interval as string
    dim number as integer

    number = left(cboVisitType.value,1) 'this is assuming no values are more than to character integers
    interval = left(right(cboVisitType.value,len(cboVisitType.val ue)-1),1) 'this pulls the value of teh first character to the right of the integer that determines the number

    if interval = "w" then interval = "ww" 'if weeks is selected ww is a way to add weeks, w only adds workday

    txtscheduledate = dateadd(inerval, number, birthdate)


    **Note**
    If you are pulling values from a subform on a form there may need to be additional references made to the controls.

  5. #5
    Join Date
    Dec 2003
    Posts
    268
    When referencing a subform from a form

    forms.formname.subformname!controlname

    So for your code it would be:
    Forms.frmVisits.subfrmInfants!txtScheduledDate

    this should work.

    also I noticed for 2-4 weeks you are adding only one week. Change your 1 to a 2-4 respectively.

    Scheduled date is on the subform along with

    I am alittle confused by some of your prior statements.
    1. Is the Main Form named frmMain or frmVisits?
    2. Is the Sub Form named frmSub or subfrmInfants?
    3. is the text box for the birthdate named Birthdate or txtBirthDate
    4. is the text box for scheduleDate or ScheduledDate (notice the xtra D)Here is the code making these assumptions
    main form is named frmVisits
    subform is named subfrmInfants
    txtBirthdate is on the main form
    txtScheduledDate is on the subform

    Select Case cboVisit.Value
    Case "1WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    Case "2WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 2, Me.txtBirthDate)
    Case “3WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 3, Me.txtBirthDate)
    Case "4WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 4, Me.txtBirthDate)
    End Select

    Hope this helps. Again if you have any questions just Postem.

    MW

  6. #6
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face

    Originally posted by mjweyland
    When referencing a subform from a form

    forms.formname.subformname!controlname

    So for your code it would be:
    Forms.frmVisits.subfrmInfants!txtScheduledDate

    this should work.

    also I noticed for 2-4 weeks you are adding only one week. Change your 1 to a 2-4 respectively.

    Scheduled date is on the subform along with

    I am alittle confused by some of your prior statements.
    1. Is the Main Form named frmMain or frmVisits?
    2. Is the Sub Form named frmSub or subfrmInfants?
    3. is the text box for the birthdate named Birthdate or txtBirthDate
    4. is the text box for scheduleDate or ScheduledDate (notice the xtra D)Here is the code making these assumptions
    main form is named frmVisits
    subform is named subfrmInfants
    txtBirthdate is on the main form
    txtScheduledDate is on the subform

    Select Case cboVisit.Value
    Case "1WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)
    Case "2WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 2, Me.txtBirthDate)
    Case “3WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 3, Me.txtBirthDate)
    Case "4WeeksVisit”
    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 4, Me.txtBirthDate)
    End Select

    Hope this helps. Again if you have any questions just Postem.

    MW

    By using this code:

    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)

    I'm still getting error saying Method or data member not found

    I have two subform

    1st subform name "subfrmInfants" which has "txtBirthDate" field

    2nd subform name "subVisit " which has "cboVisitType" and "txtScheduledDate"
    Skharva

  7. #7
    Join Date
    Dec 2003
    Posts
    268
    Here is your line of code...

    you will need to fill in 'mainForm' name though

    me.txtScheduledDate = DateAdd("ww", 1, Forms.mainform.subfrmInfants.txtBirthDate)

    the Me is applicable to both cboVisigad the txtScheduledDate controls. but seeing as txtbirthdate resides on a different subform but on the same main form this should work.

    See if this works.

  8. #8
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by Skharva
    By using this code:

    Forms.frmVisits.subfrmInfants!txtScheduledDate = DateAdd("ww", 1, Me.txtBirthDate)

    I'm still getting error saying Method or data member not found

    I have two subform

    1st subform name "subfrmInfants" which has "txtBirthDate" field

    2nd subform name "subVisit " which has "cboVisitType" and "txtScheduledDate"
    Here is the sample DB in Access 2000
    Attached Files Attached Files
    Skharva

  9. #9
    Join Date
    Dec 2003
    Posts
    268
    Skarva

    Attached you will find the fixed DB. I fixed the main form with some code and added a table

    (***NOTE*** I commented out one of your lines on frmInfants and added a new line it was only one line I didint see a need to create a copy)

    I also created some copies of some of your existing forms. Look at all of the forms/tables with a TEMP after the frm or tbl

    The table was added to keep the information regarding the visti and the date add function directly to the control box itself. The value you want to keep in there (2wk, 4wk etx) will still be stored . This is a better practice rather than querying the table you are using to drive another query that drives the form.

    It added some dynamic control to the form to be able to add days, weeks, months, years etc. to the scheduled date. May be a little over kill, but I thought it might be helpful in some other fashion in your DB.

    I did notice a couple of things though. the frmEnroll doesn't show the record navigators but allows for additinal enrollments per mother. You may want to restrict this to one or add the navigation buttons to the bottom.

    I documented everything I did in the code so it should be an easy transition over to your actual DB.

    Again, if you have any questions just let me know.

    MW
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Originally posted by mjweyland
    Skarva

    Attached you will find the fixed DB. I fixed the main form with some code and added a table

    (***NOTE*** I commented out one of your lines on frmInfants and added a new line it was only one line I didint see a need to create a copy)

    I also created some copies of some of your existing forms. Look at all of the forms/tables with a TEMP after the frm or tbl

    The table was added to keep the information regarding the visti and the date add function directly to the control box itself. The value you want to keep in there (2wk, 4wk etx) will still be stored . This is a better practice rather than querying the table you are using to drive another query that drives the form.

    It added some dynamic control to the form to be able to add days, weeks, months, years etc. to the scheduled date. May be a little over kill, but I thought it might be helpful in some other fashion in your DB.

    I did notice a couple of things though. the frmEnroll doesn't show the record navigators but allows for additinal enrollments per mother. You may want to restrict this to one or add the navigation buttons to the bottom.

    I documented everything I did in the code so it should be an easy transition over to your actual DB.

    Again, if you have any questions just let me know.

    MW
    Thanks alot for all your help.


    I think its good idea to creat another table just like you did. My question is that Do i link that tblTmpVisit to tblVisit ? And i guess i don't need to keep "tblVisitType" anymore.
    Skharva

  11. #11
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by Skharva
    Thanks alot for all your help.


    I think its good idea to creat another table just like you did. My question is that Do i link that tblTmpVisit to tblVisit ? And i guess i don't need to keep "tblVisitType" anymore.

    Also, i'm getting error when i open the "frmTempMain" is it becaues "tblTempVisitType" doesn't have PK define ?
    Skharva

  12. #12
    Join Date
    Dec 2003
    Posts
    268

    Error

    Yes this is probably occuring because there isn't a relationship between the TEMP tables and other tables. If you were to rename this it would work fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •