Continuous Form - How Bind Controls to Specific Records? 1

[kjv1611]

**************************
**IMPORTANT INFO**
Access 2007 on Windows XP Pro machine
Continuous Form Question
**************************

This is probably something really simple that I've forgotten or just never needed, but I just can't see it - yet.

Is there either a certain property (control, form, etc) or either some VBA code I can use to run a command from a command button for the specific record that the button is next to?

I guess it just has always worked for me, but it seemed before that if I placed a button on the form (in the form detail portion, where the records are displayed, that the button would run whatever command I programmed for the record it was next to. However, I'm finding on a new form I'm working on in Access 2k7, that is not the case.

No matter which button I hit (out of say 5 or 6 records showing, it's running the command ONLY on the first record.

So, what's the way to get around this? Hopefully it's something easy or strait forward that I've just overlooked.

Binding details:
**The text fields which show the record data are bound to the table records.
**The command buttons are unbound - not sure if you can bind command buttons.

Thanks for any advice/reference,

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, I may have found [HIGHLIGHT]the solution[/HIGHLIGHT] in one of my REALLY old databases, and it seems to work, I think:

Private Sub cmdLook_Click()
    [HIGHLIGHT]DoCmd.RunCommand acCmdSelectRecord[/HIGHLIGHT]
    cmdLook.Hyperlink.Address = txtFileName
    cmdLook.Hyperlink.Follow
End Sub

Now if I can just remember or find the exact context for linking to a specific worksheet with a hyperlink and/or to a specific cell... I think I have at least one other database with that one... let's see.

--

"If to err is human, then I must be some kind of human!" -Me

 

[GKChesterton]

I expect it to "just work" also. What if you use a wizard to create button and use that code?

Private Sub btnEnter_Click()
    DoCmd.OpenForm "f_cContact_Header", , , "PID_Ctact=" & Me!PID_Ctact
End Sub

This is what my Access 2003 wizard produces (plus of course some extra obfuscation which I tend to shuck). Is the Me keyword helpful?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[kjv1611]

The Me keyword isn't really helpful if the code is local to that form's VB module. If the code were stored in a module separate from the form, then you'd use something like:

Forms!frmMyFormName!ControlName - or the second ! there might be replaced by a period - I always forget, and just figure it out as I go.

I ended up getting all I wanted taken care of after some testing and digging. Though my method for opening a specific worksheet isn't all that great, it all works.

Here's what I ended up with:

Private Sub cmdLook_Click()
[green]'Create/Set Variables[/green]
    Dim Oexcel As Excel.Application
    Dim ws As Excel.Worksheet
    Set Oexcel = New Excel.Application

    DoCmd.RunCommand acCmdSelectRecord
 
[green]'Make the new excel visible[/green]
    Oexcel.Visible = True

[green]'Open the workbook for the specific record[/green]
    Oexcel.Workbooks.Open txtFileName

[green]'Find the correct worksheet and open it - THIS is the part, now, that does work, but I don't think it's the best method - I think there's a simple way to refer to the worksheet when opening the workbook, but I just can't seem to get the context correct today. [blush][/green]
    For Each ws In Oexcel.ActiveWorkbook.Worksheets
        If ws.Name = txtObjectName Then
            ws.Activate
            Exit For
        End If
    Next ws
[green]'Make sure the worksheet is maximized within the Excel window. [/green]
    Oexcel.ActiveWindow.WindowState = xlMaximized

[green]'Cleanup   [/green]
    Set ws = Nothing
    Set Oexcel = Nothing
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Replace this:
For Each ws In Oexcel.ActiveWorkbook.Worksheets
If ws.Name = txtObjectName Then
ws.Activate
Exit For
End If
Next ws
with this:
Oexcel.ActiveWorkbook.Worksheets(txtObjectName).Activate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GKChesterton]

Well ya gotta go with what works. But there has to be a direct approach.

In the snip I posted,
* the button is on a continuous form
* it opens up a new form set to the appropriate record
* the Me keyword is essential
* the Me keyword was provided by the "new button" wizard

In version 2003, noted!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[kjv1611]

[blue]PHV[/blue],

With that method, (what I originally tried as well - imagine that! ), I am getting a Run-time error:

Run-time error '91':

Object variable or With block variable not set.

I thought I had everything Dim/Set correctly, and correct references.

Just in case, here are the references I do have currently enabled in this database:
[UL][LI]Visual Basic For Applications[/LI]
[LI]Microsoft Access 12.0 Object Library[/LI]
[LI]OLE Automation[/LI]
[LI]Microsoft ActiveX Data Objects 2.1 Library[/LI]
[LI][highlight]Microsoft Office 12.0 Library[/LI][/highlight]
[LI]Microsoft Outlook 12.0 Object Library[/LI]
[LI]Microsoft Scripting Runtime[/LI]
[LI]Microsoft Office 12.0 Access database engine Object Library[/LI]
[LI]Microsoft Visual Basic for Applications Extensibility 5.3[/LI]
[LI]Windows Script Host Object Model[/LI]
[LI]Microsoft Excel 12.0 Object Library[/LI]
[/UL]

Hmm, at least one thing seems fishy there, that I didn't notice before. I've got 2 Office 12.0 Libraries enabled... could it have anything to do with this being an Access 2003 or 2000 format database which I recently converted to 2007 in the process of reprogramming it in parts?

And could THAT be what is causing the error message?

---------------------------------------
[blue]GKChesterton[/blue],

You know, I wasn't thinking when I said "not necessary." Just in most cases that I've seen, it isn't necessary. It looks like there are a few rare instances when it is necessary, as I noticed when I was trying a few different things on the same form in question.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Re: formatting typos in last post. I suppose it's still too early for thinking about sytax!
It's just prior to 7am here on Eastern Daylight Savings Time, SC, USA.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

I am getting a Run-time error
Which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Sorry, should have shown that in the last post:

Private Sub cmdLook_Click()
'Create/Set Variables
    Dim Oexcel As Excel.Application
    Dim ws As Excel.Worksheet
    Set Oexcel = New Excel.Application

    DoCmd.RunCommand acCmdSelectRecord
 
'Make the new excel visible
    Oexcel.Visible = True

'Open the workbook for the specific record
    Oexcel.Workbooks.Open txtFileName

[green]'Find the correct worksheet and open it - THIS is the part, now, that does work, but I don't think it's the best method - I think there's a simple way to refer to the worksheet when opening the workbook, but I just can't seem to get the context correct today. [/green]
    [highlight]Oexcel.ActiveWorkbook.Worksheets(txtObjectName).Activate[/highlight]

[green]'Make sure the worksheet is maximized within the Excel window.[/green]
    Oexcel.ActiveWindow.WindowState = xlMaximized

[green]'Cleanup   [/green]
    Set ws = Nothing
    Set Oexcel = Nothing
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Hmm, I think I accidentally left my comments in the code from before I took the loop out and subbed in the one line from PHV.

Here is the code with the nonworking portion highlighted:

Private Sub cmdLook_Click()
[GREEN]'Create/Set Variables[/GREEN]
    Dim Oexcel As Excel.Application
    Dim ws As Excel.Worksheet
    Set Oexcel = New Excel.Application

    DoCmd.RunCommand acCmdSelectRecord
 
[GREEN]'Make the new excel visible[/GREEN]
    Oexcel.Visible = True

'Open the workbook for the specific record
    Oexcel.Workbooks.Open txtFileName

[GREEN]'Find the correct worksheet and open it[/GREEN]
    [HIGHLIGHT]Oexcel.ActiveWorkbook.Worksheets(txtObjectName).Activate[/HIGHLIGHT]

[GREEN]'Make sure the worksheet is maximized within the Excel window.[/GREEN]
    Oexcel.ActiveWindow.WindowState = xlMaximized

[GREEN]'Cleanup[/GREEN]   
    Set ws = Nothing
    Set Oexcel = Nothing
End Sub

Until I can get the issue sorted out, I'll probably just have to go back to the loop method. Seems strange to me, but I gotta go with what works.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

You have to be sure that txtObjectName contains a valid sheet name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

I'm sure it does, as the loop that checked the name there against the worksheet names of the opened workbook works every time. Originally, I was thinking the same thing - that somehow the name wasn't correct, but it is.

Well, If I can't get it sorted out to work the way I WANT it to work, I know it'll work nonetheless.

The only thing I'd worry about will be if someone uses the database to work with a workbook with a great number of worksheets. However, since it only looks at the name of each worksheet, I wouldn't expect it to cause any problems even with large workbooks.

--

"If to err is human, then I must be some kind of human!" -Me