Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Export record from subform (A97)

    I am currently creating an Incident reporting database. I have created a form run by a couple of combos where the user selects an estate and the second combo requerys to show the roads or houses on that estate. After updating the second combo it refreshes the form to show the addresses equal to the parameters set by the combo boxes in a subform. All is well and this is running as planned.

    What I would like to do now (and naively thought it would be simple) is for the user to select one of the records in the sub form corresponding to the address they want and then press a command button to export the details to an incident logging form. The bit about transferring the details I can work out, it is just the way I can select that particular record and get the values of the fields in it.
    Jerry

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

    Re: Export record from subform (A97)

    It's not clear to me what role the "incident logging form" plays - is it inside or outside the database, and if inside, is it bound or unbound? So the following are just some broad ideas. If you want specific help, please provide more information.

    You can refer to the values of the controls for the current record as Me.ControlName on the form itself, or as Forms!frmSomething!ControlName from another form.

    Assuming that the records in the subform have a unique identifier, say ID, you can also open a recordset that selects the record for that ID. Let's say that the subform is based on a table (or query) MyData, you can do something like this:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM MyData WHERE ID=" & Me.ID)

    You can now refer to the fields of the recordset: rst!StreetName

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    The question is a little vague unless others have been following any other threads on developing this database. I don't understand what you mean about exporting the details to another form. Do you mean you want to open another form showing the related record, are you trying to update a table with specific information, or what?
    <hr>The bit about transferring the details I can work out, it is just the way I can select that particular record and get the values of the fields in it. <hr>
    Perhaps if you explain in detail what you need to do, someone can help.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    Hi Hans

    I was watching you looking at my post in "Who's Online", scary...

    I could <img src=/S/scream.gif border=0 alt=scream width=15 height=15> , I had that piece of code written up and could not get my head around "WHERE ID =", of course it is Me.ID

    Just to clarify, the incident logging form is in the current database and is unbound to the form I am working on. The form I am working on is to be used so that I can get "clean" data into the incident log as the people who will be using it are not terribly computer literate and may have a habit of inputting large amounts of spelling mistakes which will screw up the reporting aspect of the database. ( Government figures,performance indicators and all that stuff)
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    Do you mean you want to open another form showing the related record, are you trying to update a table with specific information, or what?

    I want the user to select a record from the subform, press a command button to confirm the selection and then the various fields values of the selected record will be placed in an incident logs address fields. e.g. Address1,address2,address3,address4,postcode
    Jerry

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

    Re: Export record from subform (A97)

    >> of course it is Me.ID

    Does that mean that you can take it from here, or would you like more assistance?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    Hmmm, there I was thinking I had got it and then this error popped up.

    here is my code
    Private Sub cmdSelect_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Set rst = dbs.OpenRecordset("SELECT * FROM [qrySubFormpropertyLookUp] WHERE [ID]=" & Me.ID)</span hi>



    rst!ID = Me.ID
    rst!WardID = Me.WardID
    rst!Proref = Me.Propref
    rst!BlockStreet = Me.BlockStreet
    rst!Estate = Me.Estate
    rst!NMO = Me.NMO
    rst!FlatNo = Me.FlatNo
    rst!House = Me.House
    rst!Address2 = Me.Address_2
    rst!Address3 = Me.Address3
    rst!Address4 = Me.Address_4
    rst!PostCode = Me.PostCode
    rst!Northing = Me.Northing
    rst!Easting = Me.Easting

    End Sub

    Is it me, or am I havinh a bad day?
    Jerry

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

    Re: Export record from subform (A97)

    1. Is ID the name of the field in the table and of the control on the form?

    2. Is ID numeric? If it is text, you'll need to add quotes around the value:

    ... WHERE [ID]=" & Chr(34) & Me.ID & Chr(34))

    Chr(34) is the double quote "

    3. Does the query qrySubFormpropertyLookUp refer to a form (something like Forms!...) DAO doesn't know about forms, so it sees a reference to a form as a parameter. There are ways around this, so if this is the case, tell us what qrySubFormpropertyLookUp looks like.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    Hmmm, no 3 on your list maybe the answer.

    The parent form has a combo on it that selects a value that is the parameter for the query that runs the subform. So yes it has [Forms]![frmPropIden]![cboRoad] in the query
    Jerry

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

    Re: Export record from subform (A97)

    Patt has a good example of how to handle this kind of thing in <post#=263374>post 263374</post#>

    The MSKB article for this problem is ACC: "Too Few Parameters. Expected 1" Error Message

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export record from subform (A97)

    Hi Hans

    Sorry it has taken a long time to get back to you as I took a 'sicky' on Friday . I have now got my form working. It is what we call in this office, a PEBCAK ( Problem exists between keyboard and chair).

    You were right that the query that ran the subform had a reference to a control on the parent form and therefore DAO didn't like. I realised on the way home that I did not have to reference the subform qry in the SQL statement but just had to run back to mother and take it from the original table. I just changed the code so it was just dim rs as database etc with no reference to the DAO.

    Using your code that you sent me I have was able to get the record (using me.ID) and then got the data I was trying to retrieve so that I could place in the respective fields in my incident form.

    Anyway, it works and thank you for your patience with this one
    Jerry

Posting Permissions

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