Pick Record from certain record for a parameter query (1 Viewer)

stu_c · Sep 10, 2018

Hi all
I will try and explain my issue best I can, I have got a tabular form with a list of vehicles each with a button beside each record, when the button is clicked it should pick id the IDFKVehicle ref number from each record and put through a parameter query but instead it always picks it up from the top record not the one the button is beside anyone know how to sort this?

Code:

Private Sub Command356_Click()
        DoCmd.RunMacro "MCR-FRMVehicleDetails-ServiceInput-OPEN"
    End Sub

the query does as it should but its just the form code

Ranman256 · Sep 10, 2018

the query must have the record Key in it:
select * from table where [key]=forms!myForm!txtKey

you can also open a form on that record:
docmd.openForm "fMyForm",,,"[key]=" & me.txtKey

does the macro open the query?

stu_c · Sep 10, 2018

Hello
I have done the query, but seems that it picks up the key from the first entry but not on the record next to each record

Ranman256 said:
the query must have the record Key in it:
select * from table where [key]=forms!myForm!txtKey

you can also open a form on that record:
docmd.openForm "fMyForm",,,"[key]=" & me.txtKey

does the macro open the query?

arnelgp · Sep 10, 2018

whAt does your macro do?

stu_c · Sep 10, 2018

Just opens a form with a query mate

arnelgp said:
whAt does your macro do?

June7 · Sep 10, 2018

If you are going to use VBA, why bother with macro? Just use VBA command suggested by Ranman.

Is IDFKVehicle a number type?

JHB · Sep 11, 2018

stu_c said:
..
I have done the query, but seems that it picks up the key from the first entry but not on the record next to each record

Show the query.

stu_c · Sep 11, 2018

That is not the issue I am having.

June7 said:
If you are going to use VBA, why bother with macro? Just use VBA command suggested by Ranman.

Is IDFKVehicle a number type?

Pat Hartman · Sep 11, 2018

Instead of running a macro, the click event of the button should run the OpenReport command that ranman suggested.

PS - it is poor practice to not give your form/report objects meaningful names. The name of the button should be something like:

cmdOpendReport

OR

btnOpenReport

Of course if you change the name of an object after you have created event procedures or referenced it in code or a query, you will break the code or query so you have to change the name every place it needs to be changed. That's why you should ALWAYS do this BEFORE you use the object.

stu_c · Sep 11, 2018

Pat
I have changed the code to the following, as mentioned all I need is when the button is clicked it opens a Form with the correct ID Details, currently it opens what ever record is at the top but not the one the button is beside, my button also ticks / unticks check boxes but I wanted to sort the REF number issue before anything else

All my query contains is
IDVehicle
[Forms]![FRMVehiclesInWorkshops].[IDVehicleDetails]

and other fields

Code:

Private Sub Command356_Click()
   If Me.Combo109 = "50" Then
       Me.Ready = True
       Me.Repaired = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehiclesInWorkshops"

Else
   'Variable Declaration
    Dim OutPut As Integer
    'Example of vbYesNoCancel
    OutPut = MsgBox("Was The Vehicle Serviced?", vbYesNoCancel, "Example of vbYesNoCancel")
    If OutPut = 6 Then
        'Output = 6(Yes)
       Me.Ready = True
       Me.OldDefect = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehicleDetails-ServiceInput"
       
    
    ElseIf OutPut = 7 Then
        'Output = 7(No)
       Me.Ready = True
       Me.OldDefect = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehiclesInWorkshops"
    
    Else
        'Output = 2(Cancel)
    End If
End If
End Sub

Gasman · Sep 11, 2018

Is this a continuous form?

stu_c · Sep 11, 2018

Yes it is mate and the code I am currently using just picks up the ref number from the top record not the one the button is beside

Gasman said:
Is this a continuous form?

June7 · Sep 11, 2018

I did a test with dynamic parameterized query referencing continuous form and it works - opens form to record of ID button is next to. However, I don't use dynamic parameterized query. I prefer WHERE argument of OpenForm command. If you want to provide db for analysis follow instructions at bottom of my post.

Gasman · Sep 11, 2018

The experts will be able to explain why. I had this problem, but forget the exact cause. I seem to recall it is due to bound or unbound controls, where there is really only one record, not like a datasheet.

stu_c said:
Yes it is mate and the code I am currently using just picks up the ref number from the top record not the one the button is beside

arnelgp · Sep 11, 2018

use yiur combobox name in the query.
yiu dont have IDVehicleDetails in yhe form.

stu_c · Sep 11, 2018

It's an absolute pain!

Gasman said:
The experts will be able to explain why. I had this problem, but forget the exact cause. I seem to recall it is due to bound or unbound controls, where there is really only one record, not like a datasheet.

stu_c · Sep 11, 2018

Any chance of a quick mock up of it working mate?

June7 said:
I did a test with dynamic parameterized query referencing continuous form and it works - opens form to record of ID button is next to. However, I don't use dynamic parameterized query. I prefer WHERE argument of OpenForm command. If you want to provide db for analysis follow instructions at bottom of my post.

June7 · Sep 11, 2018

I prefer you to provide your attempt for analysis.

stu_c · Sep 14, 2018

June7 said:
I prefer you to provide your attempt for analysis.

June
This is my own attempt, it loads a parameter query but still no success, were you able to get it working?

Code:

Private Sub Command356_Click()
   If Me.Combo109 = "50" Then
       Me.Ready = True
       Me.Repaired = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehiclesInWorkshops"

Else
   'Variable Declaration
    Dim OutPut As Integer
    'Example of vbYesNoCancel
    OutPut = MsgBox("Was The Vehicle Serviced?", vbYesNoCancel, "Example of vbYesNoCancel")
    If OutPut = 6 Then
        'Output = 6(Yes)
       Me.Ready = True
       Me.OldDefect = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehicleDetails-ServiceInput"
       
    
    ElseIf OutPut = 7 Then
        'Output = 7(No)
       Me.Ready = True
       Me.OldDefect = True
       Me.DateOut = Date
       DoCmd.OpenForm "FRMVehiclesInWorkshops"
    
    Else
        'Output = 2(Cancel)

    End If
    End Sub

JHB · Sep 14, 2018

Post your database with some sample data.

Pick Record from certain record for a parameter query (1 Viewer)

Registered User.

Attachments

Well-known member

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

AWF VIP

Have been here a while

Registered User.

Super Moderator

Registered User.

Enthusiastic Amateur

Registered User.

AWF VIP

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

AWF VIP

Registered User.

Have been here a while

Similar threads

Users who are viewing this thread