Please help syntax

[MCantu]

I posted before, I had not gotten a response

Here is my code :
CMID = Me.CombinedID
Forms![FRMMAIN]![CombinedID].SetFocus
DoCmd.GoToRecord acForm, FRMMAIN.CombinedID, acGoTo, CMID

This is on the onclick event of a command button on a subform.
the plan is to make the main form "Frmmain" to jump to the record that was displayed on the subform.

What am I doing wrong?

 

[avwtech]

try this

I'm not sure how this will work inside a subform but its worth a shot?

DoCmd.save
Dim CMID As String
CMID = Me.CombinedID.Value


Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CombinedID] = '" & CMID & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[MCantu]

You may be on the right track

I gave that a shot and I got "data type mismatch in critera expression"

The combinedmain field holds an autonumber.


Any other thoughts?

 

[trucktime]

In that case change:

Dim CMID As String

to:

Dim CMID As Integer

 

[john471]

Dim CMID As Integer

will cause your procedure to error if your AutoNumber falls outside the range of an integer (e.g. when you get up to AutoNumber 32,768, or if you use random AutoNumbers instead of sequential - it could happen at any time.)

AutoNumbers are Long Integers, therefore....

Dim CMID As Long

instead

Plus you will need to remove the single quoutes in your FindFirst criteria
rs.FindFirst "[CombinedID] = " & CMID

 

[ChrisO]

Is it just me or is the logic upside down?

‘Normally’… a main form / sub form is constructed on a one side / many side relationship.

That is to say, if the sub form shows any records then the main form is already at the record it should be on; clicking on a record on the sub form should not change the main form record.

It might be too much Christmas cheer at this end but is that what is really going on?

Regards,
Chris.

 

[MCantu]

Hummmm

No more error messages- but it doesn't work either.

Just sits there.

Ah, having the button there will just encourage my users to be lazy anyway..........

I'm going to keep at it.

Thanks for your help!

 

[john471]

I would agree with chris, it would seem a strange (backwards) relationship that you have set up with your form/subform. Are you able to explain your situation a little more ?

If you really want to pursue it, and if you are using avwtech's suggestion, I would check to see if in
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
"me" is the subform or the main form ??? This will depend on the location of your procedure.

But again, it is a little difficult to imagine what you are trying to achieve.

 

[MCantu]

How it's used.

My users are entering interactions they are having on the phone.
The current record is displayed on the form

On the bottom of the mainform, there is a subform (continuous form) of all the entries they have made previously.
So- at a glance, they can see their stuff.

If for some reason, they want to go back to a record they see in the subform, I want them to be able to just click the button on the subform, and have the main form display the record.

I don't want them to be able to modify in the subform because:

I have not displayed all the fields (too many- too messy. I want to compact the size of the subform so I can get as many records as possible on the screen - so they can be glanced at)

I don't want them to free text.

All the Bells and whistles are in the main form, and it keeps the information cleaner if they edit there.


Attached is screenshot.

Tanks for any help.

 

[ChrisO]

G’day MCantu

This sort of thing seems to work in the small attached demo: -

Option Explicit
Option Compare Text


Private Sub cmdGotoRecord_Click()

    Me.Parent.Filter = "CompanyID = " & [CompanyID]
    Me.Parent.FilterOn = True

End Sub

Hope that helps.

Regards,
Chris.

 

[MCantu]

This may work

It applys a filter... which does not allow my user to use my command buttons to go to the next record... however...

Maybe I can work something in the command buttons for record navigation.

for "new record" commands, I can turn the filter off and then add the new reocord... (if that is even necessary- which It may not be, I have to check.)

I can turn the filter off when the navigation command buttons are clicked- but then I would automatically go to the first record....


Ok- I am a step closer now. I just have to figure out how to stay on that record once I turn the filter off.

I will have to wait until my brain is back from Newyears.

Thanks! This did help!

 

[ChrisO]

Second try: -

Option Explicit
Option Compare Text


Private Sub cmdGotoRecord_Click()

    Me.Parent.RecordsetClone.FindFirst "CompanyID = " & [CompanyID]
    
    If Not Me.Parent.RecordsetClone.NoMatch Then
    [color=green]'   Me.Parent.Bookmark = Parent.RecordsetClone.Bookmark[/color]
        Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
    End If

End Sub

New demo attached.


To be consistent the line was changed.

Regards,
Chris.

 

[ChrisO]

Well here’s another variation…(Guess who gets bored sometimes?)

This one apparently does away with the command button, which it doesn’t, and allows the user to click on any part of the continuos form record. (Bigger sweet spot, one might say, and without the clutter.)

Hope that helps or gets close to what you require.

Regards,
Chris.

 

[MCantu]

This was what finally worked- part of it my fault

I wanted to come back and post what worked and why.
I have learned so much from this board it is not funny.
Learning from the mistakes of others is one of the things that has helped me along

1.) I had to change my subform
-I had my sub form based upon another query. Why? I dunno. I was designing the database and changing its purpose as I went. This detail was forgotten, and based upon the posts above talking about how the form and subform were related I thought.. huh- I better check what that subform is based on.....


2.) Once changed, this is what worked :

Private Sub cmdGotoRecord_Click()

Me.Parent.RecordsetClone.FindFirst "CompanyID = " & [CompanyID]

If Not Me.Parent.RecordsetClone.NoMatch Then
' Me.Parent.Bookmark = Parent.RecordsetClone.Bookmark
Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
End If

End Sub

This works like a DREAM. It's exactly what I want.

ThankS!!!


To all the posters- I owe you all a cup of coffee!


Thanks!

 

[Lister]

Thanks ChrisO, example 3 was just what was needed in a app of mine. Great bit of outside thinking there mate