I am trying to use a command button to execute a query for each line item that is returned in a continuous form. The query associated with the command button references a field in a line in the continuous form. The problem I am running into is that the code references the first record returned no matter what line of the continuous form I am trying to query off of.

Example, if there are 15 line items returning values for this field. When I push the button associated with items 2-15 it returns the value seen in field 1 everytime.

Am I trying to do the impossible, or is there a way to accomplish this task?

Thank you,
KellJW

I do the same thing and this is the code I use:

'-----
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmDetails"

    stLinkCriteria = "[DetailsID]=" & Me![DetailsID]
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'-----

This works fine (for a number field). What code are you using?


mmcdonal

I should mention that in the continuous form, in your case, there would be 15 buttons, one on each record.


mmcdonal

Right now the only code associated with these buttons is a simple message box to display the value in the field. I have run into this problem before and worked around it, but never really got to the bottom of the issue. In this case, the 15 buttons are what I want and need so i will give this a shot and hope I do it right.

Thanks.

Here is the code I have for the button. The button is called 'DataEntry'.

If IsNull(Me.DataID) Or Me.DataID = "" Then
    DoCmd.OpenForm "RGAData", acNormal
    Forms![RGAData]![RGANum] = Me.RGANum
    Forms![RGAData]![CAT] = Me.CatNum
    Forms![RGAData]![FrSer#] = Me.DateCode
    Forms![RGAData]![Comm] = Me.EvalDesc
    ElseIf Len(Me.DataID) > 1 Then
        MsgBox Me.DataID
        MsgBox Me.LineNum
        'DoCmd.OpenForm "RGAData2", acNormal
End If

As you can see I am trying to populate a form based off of the 'DataID' number which is automatically generated each time a new record is written to the table. "RGAData" and "RGAData2" are physically the same form, the only difference is that RGAData2 is populated from pre-existing data in a table.

I don't know if I am just having difficulty following what you are doing in your code, I am a rookie, or if maybe I didn't explain well enough. I hope the snippit of code above clarifies.

KellJW

I would grab the parameters first in a variable, and then pass the variable to the form before it is opened. I don't think this code is doing the trick:

    DoCmd.OpenForm "RGAData", acNormal
    Forms![RGAData]![RGANum] = Me.RGANum
    Forms![RGAData]![CAT] = Me.CatNum
    Forms![RGAData]![FrSer#] = Me.DateCode
    Forms![RGAData]![Comm] = Me.EvalDesc

I would do this:
Dim lRGA As Long
Dim lCat As Long
Dim dDateCode As Date
Dim sEval As String
Dim sLink As String

lRGA = Me.RGANum
lCat = Me.CatNum
dDateCode = Me.DateCode
sEval = Me.EvalDesc

sLink = "[RGANum] = " & lRGA & " AND [CatNum] = " & lCat & _
        " AND [DateCode] = " & "#" & dDateCode & "#" & _
        " AND [EvalDesc] = " & "'" & sEval & "'"

DoCmd.OpenForm "RGAData", acNormal, , sLink

You will have to check the syntax of the DoCmd to make sure you put sLink in the WHERE section.

Does this work?
Also, I would take the variables for the MsgBox seperately and then pass them to the MsgBox with formatting so the user doesn't get two message boxes.



mmcdonal

I have altered my code so that it reflects exactly what you have above, with the exception of variable formats. I am using the msgbox to simply display the information that I want the code to read and react on. As you had indicated before, I am receiving 15 buttons, one for each record, which is what I want. Even when coding as you had indicated, the msgbox is only displaying the information for the first record returned when any of the 15 buttons is "clicked". I want the message box to display the information for only the record it is being generated with. If I click button 15, I want to see the CAT number for record 15. Instead, when I click button 15 (or any other button), it displays the CAT number for record 1 only.

The problem is not getting the form "RGAData" to pop-up with pre-populated cells. The problem is that no matter which records button I use, it always populates with information from record 1.

KellJW

I think I would have to see the continuous form at this point. Using the code above, I get the next form to open with only the selected record.


mmcdonal

Can I email you the Access file, it is 10,500KB?

Can you create a copy of it, then delete most of the data, and then mail me one about 2MB?


mmcdonal