Select current record in datasheet and show in main form?

[mccallradar]

Hello everyone,
I have a query that runs from the main form when the command button is clicked. It displays the results in datasheet view. I want the user to double click a specific record and jump to that specific record in the main form. How do I do this? Sorry, if this is a dumb question. Please help, my boss is breathing down my neck. The main form is called Contracts. Each record has a unique ID.

Thanks,
Jason

 

[boblarson]

My question is - are your main form and subform linked? If so, you are barking up the wrong tree. A main form/subform setup is primarily meant to be able to display parent records (main form) and the "many" related records (subform).

Also, you can have an unlinked main form with one, or many, subforms that are just displaying data and not dependent on the main form.

In the situation you describe it makes it sound like the two do have a link but you are wanting to use it in a manner inconsistent with the way it is designed. Can you describe, in greater detail (and with specific examples) what it is you are really after? I don't think what you're describing is exactly the right way to approach it. But, without specific examples, it is very difficult to determine whether that analysis is correct, or not.

 

[mccallradar]

My fault for not being clear. This is my first forum question. The Main form is NOT linked to a subform. There is NO subform. The user clicks a command button on the main form (Contracts) and that command runs and opens the query, i.e. runs SummOpenContracts. It is displayed in datasheet view in front of the main form.

Once the query command runs and displays the records in datasheet view, I want the user to be able to double click inside of the query(datasheet) and go directly to the record that they just double clicked to view the entire record in the Contracts form.

Should I attach a screen photo?

Thanks,
Jason

 

[mccallradar]

Screen Pics from DB

Attached are two screen shots. Thanks for the help. I am stumped.

-Jason

 

[boblarson]

Okay, the explanation and screenshots helped. You need to build a form (you can display it as datasheet so it will LOOK like a query) instead of opening the query. The reason is that Queries don't have EVENTS, but forms do. So, with a form, you can set the double-click event on the one text box to do the action you require.

DoCmd.OpenForm "YourFormForEditing", acViewNormal,,"[YourPrimaryKeyFieldNameHere]=" & Me.YourTextBoxNameHere

 

[mccallradar]

Runtime Error

I did this but I get a runtime error 3075, syntax error. Missing operator in query expression. Here is the code:

Private Sub FullBuyerName_DblClick(Cancel As Integer)
DoCmd.OpenForm "Contracts", acViewNormal, , "RecordID=" & Me.FullBuyerName
End Sub

Please Help. I think we are close.

Thanks,
Jason

 

[boblarson]

I did this but I get a runtime error 3075, syntax error. Missing operator in query expression. Here is the code:

Private Sub FullBuyerName_DblClick(Cancel As Integer)
DoCmd.OpenForm "Contracts", acViewNormal, , "RecordID=" & Me.FullBuyerName
End Sub

Please Help. I think we are close.

Thanks,
Jason

DoCmd.OpenForm "Contracts", acViewNormal, , "[RecordID]=" & Me.FullBuyerName

 

[mccallradar]

I still get a syntax error? Is there a way to make a macro work for the event? What code would I use for the where condition in the macro? I can make it open the form as macro but not go to the specific record.

Here is the code as it exists from our previous discussions:

Private Sub FullBuyerName_DblClick(Cancel As Integer)
DoCmd.OpenForm "Contracts", acViewNormal, , "[idsBuyerTblID]=" & Me.FullBuyerName
End Sub

Maybe there is a whole different way of doing this?

Thanks,
Jason

 

[mccallradar]

screen shot 2

Here is a screen shot of what I am trying to do.
Thanks,
Jason

 

[mccallradar]

bookmark to locate a record error 3464

I tried using a bookmark to locate the record. Here is the Code:

Private Sub FullBuyerName_DblClick(Cancel As Integer)
Dim MyRS As Recordset, Criteria As String
Set MyRS = Me.RecordsetClone
Criteria = "[idsBuyerID] = '" & Me.ContractNumber & "'"
DoCmd.OpenForm "Contracts", acNormal
MyRS.FindFirst Criteria
If Not MyRS.NoMatch = True Then
Me.Bookmark = MyRS.Bookmark
End If
MyRS.Close
End Sub

But I get the following new error: Runtime error 3464 - datatype mismatch in criteria expression. When I debug it yellow highlight to: MyRS.FindFirst Criteria

What am I doing wrong? I am at the end of my rope.

Any thoughts? I REALLY appreciate it.
-Jason

 

[sliquester]

datatype mismatch in criteria expression usually means that you either need to add or remove the single quotes around the value.

In your case it is probably

Criteria = "[idsBuyerID]=" & Me.ContractNumber

Instead of: Criteria = "[idsBuyerID] = '" & Me.ContractNumber & "'"

 

[mccallradar]

Thanks. Worked great! I should have thought of that. Everyone was great in helping me.