{SOLVED} Go to Selected Record in a another form.

[mailing]

Hi All,
I have a cmd button "Details" on a form listing all available records; how do I direct it to the selected record rather than just open a form?
Below is the code for Open the form I have so far. Thanks for the help!
maili
================================
Private Sub cmdDetails_Click()
On Error GoTo Err_cmdDetails_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PromosMainForm"

stLinkCriteria = "[PromoCode]=" & Me![PromoCode]
DoCmd.OpenForm stDocName, , stLinkCriteria

Exit_cmdDetails_Click:
Exit Sub

Err_cmdDetails_Click:
MsgBox Err.Description
Resume Exit_cmdDetails_Click

End Sub

[pks00]

Not sure what u mean here, given a PromoCode, u want to find that record in the current form?

if thats what u mean then u could use recordsetclone and bookmarks
e.g.

Private Sub cmdDetails_Click()
 
    dim rs as dao.recordset
 
    set rs=me.recordsetclone
    rs.findfirst "[PromoCode]=" & Me.PromoCode
    if rs.nomatch=false then
        me.bookmark = rs.bookmark
    else
        msgbox "not found"
    end if
 
    rs.close
    set rs=nothing
end sub
 

[mailing]

Thanks, I will try it.
maili

[newburydave]

I'm having a similar problem. I am programing a Contact Management database for a friend and one of the problems I am encountering is the one described above.

I have successfully accomplished this operation in another Prospecting database I have written. In that database I use a macro to OpenForm with the Condition set to [ID]=[Forms]![ID].

This switches between two different prospecting forms generating from different Queries which hit the same two Tables.

I tried to use the same syntax in the Contact Management database when going from the Main Form, which references all the fields in the main Table, to a secondary Form, which references only the fields in a second Table.

The link between the two tables is a "Lookup Column" which pulls the first and last names of agents from the secondary table.

I want to use this Lookup Column entry in the Main form to open a secondary form bound to the secondary database and selecting the entire record from which the Lookup Column entry was selected. I have tried the same syntax that worked in the other database but when I run the macro access pops up an entry box asking for the contents of the form field which contains the Lookup Column value.

1. Is it possible to use the value in a Lookup Column as a table to table link in this fashion?
2. What is the proper syntax to accomplish this?

My SQL skills are very rusty so I am using the wizars where ever possible.

thanks
dave

[newburydave]

Correction to my previous post. The 'Where Condition' statement is [ID]=[Forms]![referring form name]![ID].

[ID] is the key field. I believe I am seeing something; the statement after the = is the key field (the field linking the two forms) in the Form you are using for the source of the data and the [ID] before the = referrs to the cooresponding field in the target Form.

Is this correct?

[newburydave]

It worked!

The syntax that worked is [target field name]=[Forms]![originating form]![originating field name]. Where the value in the 'Originating Field' and the 'Target Field' are to be equal.

Thank you all. Reading your input in the previous posts in this thread helped get my thinking going in the right direction.

The full method I used is this:

Go to Macros;
Create a new macro;
In the 'Actions Column' select 'OpenForm' from the drop down list;
In the 'Action Arguments' section at the bottom of the screen, on the 'Form Name' line select the Target Form from the drop down list;
On the 'Condition is' line enter the SQL statement above.
In Comand Button creation select 'Miscelaneous' - Run Macro - (your new macro name) from the list.

I found that when the Originating Form field is a 'Lookup Column' referencing a field in the Secondary Table, The correct 'Target Field' is the [ID] field since that is the numeric value stored in 'Lookup Column' field in the Originating Form.

Hope this helps.

[PEB]

Hi if your promocode is a text so the line :
stLinkCriteria = "[PromoCode]=" & Me![PromoCode]

Should be:
stLinkCriteria = "[PromoCode]='" & Me![PromoCode] &"'"

Hi All,
I have a cmd button "Details" on a form listing all available records; how do I direct it to the selected record rather than just open a form?
Below is the code for Open the form I have so far. Thanks for the help!
maili
================================
Private Sub cmdDetails_Click()
On Error GoTo Err_cmdDetails_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PromosMainForm"

stLinkCriteria = "[PromoCode]=" & Me![PromoCode]
DoCmd.OpenForm stDocName, , stLinkCriteria

Exit_cmdDetails_Click:
Exit Sub

Err_cmdDetails_Click:
MsgBox Err.Description
Resume Exit_cmdDetails_Click

End Sub

[mailing]

It worked!
I tried the Macro, it worked. However I need the vb code, so I tried the code.
(Also, there is a process to convert Macro into vb:
Go to the macro u want to convert
click on Tools on menu bar,
choose Macro, convert to visual basic)

They are both working.
Thank you all.
Cheers

[NeoPa]

It worked!
I tried the Macro, it worked. However I need the vb code, so I tried the code.
(Also, there is a process to convert Macro into vb:
Go to the macro u want to convert
click on Tools on menu bar,
choose Macro, convert to visual basic)

They are both working.
Thank you all.
Cheers

That is brilliant - I wish I'd known that all these years.
It's a bit late for me now but using that is a very good way to start learning about VBA in Access and especially the common library routines.
(D*mn - I wish I'd known that before!)
Thanks for that Mailing.

-Adrian.

[MMcCarthy]

That is brilliant - I wish I'd known that all these years.
It's a bit late for me now but using that is a very good way to start learning about VBA in Access and especially the common library routines.
(D*mn - I wish I'd known that before!)
Thanks for that Mailing.

-Adrian.

Hey Adrian

I knew but I wasn't going to let you into all my secrets now was I.

Actually it's been so long since I used macros for anything. IT managers hate them as they are so hard to maintain. I prefer to use code myself anyway.

Mary

[NeoPa]

Don't get me wrong Mary - I'm 100% with you on that.
I was thinking that, for a beginner (I wish I'd known back then for myself), learning which procedures and objects are available in the main libraries, and some of the basic forms that code takes, can be a real boost to getting going in a language.
I started from scratch in Excel and was helped enormously then by recording macros, which I then checked out in the VBA window.
You get the system to create code for a job which you know, then peruse the code until you understand what it's doing.

[MMcCarthy]

Don't get me wrong Mary - I'm 100% with you on that.
I was thinking that, for a beginner (I wish I'd known back then for myself), learning which procedures and objects are available in the main libraries, and some of the basic forms that code takes, can be a real boost to getting going in a language.
I started from scratch in Excel and was helped enormously then by recording macros, which I then checked out in the VBA window.
You get the system to create code for a job which you know, then peruse the code until you understand what it's doing.

I agree completely. I really just meant that is has been so long since I'd used macros that I had forgotton the macro to code feature existed. My problem now days is when someone asks me a macro question I can't remember the syntax and I have to check it out.

Mary