docmd.open

[at51178]

I am trying to open up a form from another form
but I need the second form to filter based on what the first form has in agentname textbox this is what I have so far can someone tell me what I am doing wrong.

Private Sub cmdnext_Click()
Dim stdocname As String
Dim stform As String
stdocname = "frmquestion1"
stform = "forms!frminformation!agentname"
DoCmd.openform stdocname, , , ("[Agentname] = stform&quot


thanks

 

[Junior1544]

DoCmd.openform "frmquestion1"
forms!frmquestion1.filter = "[Agentname] = " & forms!frminformation!agentname
forms!frmquestion1.filteron = true

try this out, let me know...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[at51178]

Sorry that didn't work

I get a popup box that says
"enter parimater value"

 

[JeremyNYC]

James's method should work, but it will first load all of the data into the form and then filter to just the data you want. If you want to just open directly to what you need, you can do this
DoCmd.OpenForm stdocname, , , "Agentname = '" & Forms!frminformation!agentname & "'"

The reason this will work is that the Where clause separates out the data in the way Access needs. That is, Access has to evaluate the forms!frminformation!agentname portion before passing it to the docmd.openform method. If you put it in quotes it will pass it literally, and I doubt you have any agents with such funny looking names!

Also, you'll notice that I put in single quotes here. The reason for this is that I've assumed that agentname is a text field, and if that's the case, Access (Jet, actually, but that's another matter) needs the single quotes to offset a text item.

Also, I got rid of the brackets. Since you've named your field without any spaces (good habit), you don't need the brackets. They won't cause any problems, but I just don't like looking at them.

Assuming you Agent's name is Sally Struthers, your form will get passed the following where clause:
Agentname = 'Sally Struthers'
and that's just what you want.

If my assumption is wrong and agentname is not a text field, get rid of the single quotes and that should work just fine.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Junior1544]

one thing to note... (btw, i like Jeremy's solution better...)

With the single quote's... you can't have some one with a ' in their name sent to the field or it'll error out...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

James,

True, unless you use the replace function (later versions of access) or some complicated code I got from someone to clear those things and pipes out from stuff between the quotes (a97).

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[at51178]

Sorry guys but i am doing what you told me I am not sure what I am doing wrong

this is the code that I am using

Private Sub Command1_Click()
Dim stdocname As String
stdocname = "frmquestion1"
DoCmd.OpenForm stdocname, , , "agentname = '" & Forms!frminformation!Agentname & "'"

and it should work but I still get a pop up box asking me to enter a parimater value everytime I press the command button

 

[JeremyNYC]

at,

It sounds like the recordsource for your form has a parameter missing. What is that recordsource (need to see the sql here)?

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[at51178]

if you want I could send a copy of the database to you if you give me your email address

 

[Junior1544]

here's another idea.. can you open the form from the database window(with no other window's open)... or will it still ask you for the parameter?

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

at,

No, please do not send me the database. Open the query in design view. Then view the sql of the query (view menu).

Post that text back here.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[at51178]

I figured it out

I found out where the error was my agentname field in my table was spelled differently than the textbox name for agentname in the form that is why it didn't work

I Still have to fool around with it through out the whole database but I understand why it didn't work
thanks for all your help

 

[KUZZ]

try this:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "MACHINESHOP"
stLinkCriteria = "[pt#]=" & "'" & [PT#] & "'"
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit, acNormal

Kuzz

 

[at51178]

Sorry to bother you guys

but I came up with a little problem

I am able to filter names that are already in the database from one form to another that is no problem

but the way it is set up right now is that
the user has to type in their name into the database
they then press the command button which opens the question1 form and filter's their name or record to the next form.

what I am finding out is that when I open the database which opens frminformation in add mode I enter in a user name and when I press next I see the first name in the table I will post the sql code and the vb code to get a better idea of what I am try to do. thanks

SQL CODE


SELECT [tblDatabase].[AgentName], [tblDatabase].[Question1], [tblDatabase].[Question2], [tblDatabase].[Question3], [tblDatabase].[Question4], [tblDatabase].[Question5], IIf([question1]="Correct","30","0&quot AS q1score, IIf([question2]="Correct","30","0&quot AS q2score, IIf([question3]="Correct","40","0&quot AS q3score, ([q1score]-[q1score]+[q1score]+[q2score]+[q3score]) AS TtlSc
FROM tblDatabase;



vbcode

Private Sub cmdnext_Click()
Dim stdocname As String
stdocname = "frmquestion1"
DoCmd.openform stdocname, , , AgentName = Forms!frminformation!AgentName
DoCmd.Save
Dim closeinformation As String
closeinformation = "frminformation"
DoCmd.Close acForm, closeinformation, acSaveYes