Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    42

    Unanswered: Bound Control Problem...Help!

    Hi. I am trying to bind a control when and change event is triggered from a combo box. I have a main form with 3 sub forms on it. The combo box is on the main form and the controls I want to populate are on one of the sub forms. I am using the following code:

    Private Function BindInsured_Controls()
    '************************************************* ***
    'Developed by:
    'Purpose: Bind Controls to form record source
    'Last Updated:
    '************************************************* ***
    Set db = CurrentDb

    strSQL = ""

    strSQL = "SELECT tblCCS_Claims.CCS_ClaimNo, tblCCS_Claims.Insrd_PolicyNum, tblCCS_Claims.Insrd_ClaimNum, tblInsured.Insrd_LName, tblInsured.Insrd_FName, tblInsured.Insrd_Phone, tblInsured.Insrd_Mobile, tblInsured.Insrd_Email, tblInsured.Insrd2_LName, tblInsured.Insrd2_FName, tblInsured.Insrd2_Phone, tblInsured.Insrd2_Mobile, tblInsured.Insrd2_Email, tblInsurance_Cos.InsurCo_Name, tblCCS_LossTypes.LossType_Desc "
    strSQL = strSQL & "FROM tblCCS_LossTypes "
    strSQL = strSQL & "INNER JOIN (tblInsurance_Cos INNER JOIN (tblInsured RIGHT JOIN tblCCS_Claims ON tblInsured.CCS_ClaimNo = tblCCS_Claims.CCS_ClaimNo) ON tblInsurance_Cos.InsurCo_ID = tblCCS_Claims.Insrd_InsCo_Id) ON tblCCS_LossTypes.LossType_Id = tblCCS_Claims.LossType_Id "
    strSQL = strSQL & "WHERE (((tblCCS_Claims.CCS_ClaimNo)=" & gdblCCS_ClaimNum & "));"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

    sfrmInsrd.RecordSource = strSQL ' Set recordsource

    With rst.Fields

    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_LName

    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_FName

    End With

    Set rst = Nothing

    Set db = Nothing

    End Function

    I keep getting an error message saying that Access cant find the field "Insrd_LName". Is there somethign I am over looking or am I going about this all wrong?

    Any thoughts?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    interesting idea... never tried it your way.

    meanwhile, replace
    .Insrd_LName

    with
    !Insrd_LName



    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Bound Control Problem...Help!

    Originally posted by waderw24
    Hi. I am trying to bind a control when and change event is triggered from a combo box. I have a main form with 3 sub forms on it. The combo box is on the main form and the controls I want to populate are on one of the sub forms. I am using the following code:

    Private Function BindInsured_Controls()
    '************************************************* ***
    'Developed by:
    'Purpose: Bind Controls to form record source
    'Last Updated:
    '************************************************* ***
    Set db = CurrentDb

    strSQL = ""

    strSQL = "SELECT tblCCS_Claims.CCS_ClaimNo, tblCCS_Claims.Insrd_PolicyNum, tblCCS_Claims.Insrd_ClaimNum, tblInsured.Insrd_LName, tblInsured.Insrd_FName, tblInsured.Insrd_Phone, tblInsured.Insrd_Mobile, tblInsured.Insrd_Email, tblInsured.Insrd2_LName, tblInsured.Insrd2_FName, tblInsured.Insrd2_Phone, tblInsured.Insrd2_Mobile, tblInsured.Insrd2_Email, tblInsurance_Cos.InsurCo_Name, tblCCS_LossTypes.LossType_Desc "
    strSQL = strSQL & "FROM tblCCS_LossTypes "
    strSQL = strSQL & "INNER JOIN (tblInsurance_Cos INNER JOIN (tblInsured RIGHT JOIN tblCCS_Claims ON tblInsured.CCS_ClaimNo = tblCCS_Claims.CCS_ClaimNo) ON tblInsurance_Cos.InsurCo_ID = tblCCS_Claims.Insrd_InsCo_Id) ON tblCCS_LossTypes.LossType_Id = tblCCS_Claims.LossType_Id "
    strSQL = strSQL & "WHERE (((tblCCS_Claims.CCS_ClaimNo)=" & gdblCCS_ClaimNum & "));"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

    sfrmInsrd.RecordSource = strSQL ' Set recordsource

    With rst.Fields

    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_LName

    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_FName

    End With

    Set rst = Nothing

    Set db = Nothing

    End Function

    I keep getting an error message saying that Access cant find the field "Insrd_LName". Is there somethign I am over looking or am I going about this all wrong?

    Any thoughts?

    Thanks in advance!
    Do you know which line is causing the error?

    If you can open the recordset then the SQL statement is OK and you can concentrate on the assignment of the subform properties. If not then I would put it into a query and run it.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Sound like a typo - double check the existance of "Insrd_LName" in the table "tblInsured".

    Is this also a typo on one of these controls?
    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_LName
    Set sfrmInsrd.txtEst_Ins1.ControlSource = .Insrd_FName
    When you get to this point - should it just be:

    ...txtEst_Ins1.ControlSource = "Insrd_FName"
    Since it seems that you know the name of the field.

    !Insrd_FName - binds the control to the value of the Insrd_FName field in the first record but not to the field itself. ie- "John" but not Insrd_FName.

  5. #5
    Join Date
    Aug 2003
    Posts
    42
    That did it Rockey.....I had to use "Insrd_LName" using quotes where as before I was trying to use the bang symbol.

    Thanks for the help all!
    Last edited by waderw24; 02-21-04 at 18:50.

  6. #6
    Join Date
    Aug 2003
    Posts
    42
    Question...Any ideas on other ways to do this that may be better? The reason I am doing this is b/c I have a main form, with 3 sub forms. The main form is used for new estimates and also to update previously submitted estimates so I can't have the form bound to the controls during the creation of a new estimate but I need to return the values when the user wants to update a previously submitted estimate. Does that make sense? I am always looking for better ways to code so all thoughts are welcome.

    Thanks!

  7. #7
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    For starters - not sure what is needed besides this:


    Private Function BindInsured_Controls()
    strSQL = ""

    strSQL = "SELECT tblCCS_Claims.CCS_ClaimNo, ....

    sfrmInsrd.RecordSource = strSQL ' Set recordsource

    sfrmInsrd.txtEst_Ins1.ControlSource = "Insrd_LName"

    sfrmInsrd.requery

    End Function

  8. #8
    Join Date
    Aug 2003
    Posts
    42
    so on the change event I just requery instead of going through the controls each time?

  9. #9
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    sorry but confused...
    going through the controls each time?

  10. #10
    Join Date
    Aug 2003
    Posts
    42
    as in binding the controls everytime in the change event?

  11. #11
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    I may be missing something but I think requering the form after changing the form's recordsource should work for you.

Posting Permissions

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