Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfering Records (2000)

    Hello, I have a list box (list2) on a form that get it's detail from a query. Query has two fields each taken from two tables; field IFA and field Area Office. I have a form 'panel' that has two fields IFA and Area Office (both are text data), a sub form sits on this in which contact details are recorded. I want to be able to open the Panel form with the IFA and Area Office record displayed triggered by clicking on the IFA and Area Office detail in the list box (list2). I have done a similar previously but I can't get it to work this time. The code I am using is...

    Private Sub List2_Click()

    sDocName = "panel"
    sLinkCriteria = "[IFA]=" & Me![list]

    DoCmd.OpenForm sDocName, , , sLinkCriteria


    End Sub


    Many thanks Darren.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Darren
    Try this version below, it may help.

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "panel"
    stLinkCriteria = "[IFA]=" & "'" & Me![list] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Cheers Dave, this ain't playing ball either?

    Darren

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    How about posting a small demo (zipped under 100k)
    Take out any personal and none relevent forms,tables etc and I'll see what I can do.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Cheers Dave, attached...
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    I think I've done what you asked, you'll have to check the attachment.
    A couple of things:

    Be careful with your naming conventions, there are reserved words and references within Access you should avoid, like

    Date

    Having a field name such as this will cause you problems later on, try to use the following examples.

    txtDate instead of Date
    frmPanel instead of panel
    qry????
    tbl????

    etc etc.

    You query also has obscure names also, although you think you have IFA twice, you don't really because the query will add the name of the table also if two fields are named the same.
    If you want to use this method it's fine with only one field.
    You can add your own names by adding IFA: before the field name.

    Not sure if I've made alot of sense but good luck anyway.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Splendid Dave, TVM. thanks for the direction too, does make sense, I am being lazy!

    Thanks again.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Darren

    It's a pleasure, glad I could help.

    I was lazy for a long time, but through that, caused me untold problems and increased my work load.

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

    Re: Transfering Records (2000)

    Dave has already solved your problem and given some excellent advice. Perhaps I may add some more. I noticed that you don't have Option Explicit in your form module, and that the variables sDocument and sLinkCriteria are not declared in the event procedure.

    I strongly recommend you to select Tools | Options... in the Visual Basic Editor, and to check Require Variable Declaration. This will add a line <font face="Georgia">Option Explicit</font face=georgia> at the top of all new modules you create (you will have to add it manually to existing modules).
    This option will force you to declare each variable you use explicitly. This may seem like a pain in the <img src=/w3timages/censored.gif alt=censored border=0> initially, but it will save you a lot of grief in the long run. If you don't force explicit declarations, small typing errors can make your code behave in an unexpected way. For example:

    Dim lngTest As Long, lngResult As Long
    lngTest = 4
    ...
    ...
    lngResult = lngTsst + 3

    If you don't force declarations, VBA will create a new variable lngTsst for you, with default value 0, so lngResult will be 0+3=3 instead of 4+3=7. If you force declarations, VBA will highlight lngTsst and show an error message "Variable not defined".

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfering Records (2000)

    Cheers Hans, advise noted and hopefully embedded in memory.

    Thanks Darren.

Posting Permissions

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