Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking 2 fields and autopopulating (2000)

    I have 2 tables - TblFeeSched and TblRequest - and both have a field called request number that I want to link. The req number in the Tbl request is an autonumber and the reqnumber in Tbl Fee Sched is a number. These reqnumber fields are in forms and I want to link them and they are in forms that mirror their table names. When I open the Frmrequest and input information, I have a command button that opens the FrmFeeSched. What I want to do is when I open the FrmfeeSched , I want to autopopulate the request number on that that form. I guess that the answer would be to probably be put some kind of code on the load form part of the FrmFeeSched. Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking 2 fields and autopopulating (2000)

    Welcome to Woody's Lounge!

    The easiest way to do this would be to make the Fee Schedule form a subform of the Request form. You would link them by setting the Link Master Fields and Link Chield Fields property of the subform to the name of the request number field. Access would take care of the rest, no code needed.

    If you prefer separate forms, see <post:=513,200>post 513,200</post:>. You'll find an example of the code needed there, and you can download a database demonstrating its use.

    Post back if you have further questions.

  3. #3
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    Hans,

    Works great.

  4. #4
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    Quick question - On this form I have a command button that is a find record. However, when I insert cursor in the field I want to search it gives me this answer - Can't find or replace now. Why is it doing this?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking 2 fields and autopopulating (2000)

    The moment you click the command button, the field you want to search no longer has the focus. You can set the focus back by inserting

    Screen.PreviousControl.SetFocus

  6. #6
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    this is what the command button event procedure says. I am confused because I don't know visual basic and don't know where to put this statement.

    Private Sub Command53_Click()
    On Error GoTo Err_Command53_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command53_Click:
    Exit Sub

    Err_Command53_Click:
    MsgBox Err.Description
    Resume Exit_Command53_Click

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking 2 fields and autopopulating (2000)

    The code looks OK, if a bit old-fashioned (that's not your fault, it's Microsoft's - they still use Access 2 and Access 95 code even in Access 2003).

    I think we'd have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  8. #8
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    Here is the DB.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linking 2 fields and autopopulating (2000)

    The problem is that the form's data entry property is set to "yes'.

    This means that when you open it it does not show any exisitng records, so you also cannot search the existing records.
    Regards
    John



  10. #10
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    If I change those 2 forms to where they have their data entry set to yes that will work but what I am trying to see is this scenario. I want to open the form up where a fresh form appears without data but to be able to search for a req number through a find recordcommand button.. Also I have a command button on the switchboard that opens this form in the edit mode.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking 2 fields and autopopulating (2000)

    You can set the Data Entry property of the form to No and put code in the On Load event of the form to move to a new record:

    Private Sub Form_Load()
    RunCommand acCmdRecordsGoToNew
    End Sub

    Existing records will be available to be viewed and searched, but the form will open to a blank new record.

  12. #12
    New Lounger
    Join Date
    Nov 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking 2 fields and autopopulating (2000)

    Thanks Hans that worked.

Posting Permissions

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