Usingpop-up form to filter form (1 Viewer)

[ahuvas]

Hi

I wanted to create a input/pop-uo form (frmSearchForm) that would open when I click on a button from the switchboard form.

This formSearchForm would give me the opportunity to enter a 3 digit ParticipantID and a button that I could click to "search/filter" another form called frmAthensQuestionnaire.

When I clicked search, I would like to have some code to check whether this is valid or not (i.e. whether this ParticipantID exists or not). If not valid it would return a message "NO records found". If valid, open my form frmAthensQuestionnaire with this number passed across as a filter/ WhereCondition.

Initially I had a simple comobo box at the top of the form that would list all the participant ID's and would filter the form which I selected a record but one of the research assistants is duimb as ... and would keep forgetting to use the pull down menu and would tr to enter data. Then I tried to run the form from a query so when I clicked on the button on the switchboard form it would query which ID I would look for and then open it at that record, however the form would open at "000" if I entered a non-sense value or didnt enter anyy value so...

here I am trying to write code for this and I have no idea. Can someone please help me?

 

[ajetrumpet]

Hi

I wanted to create a input/pop-uo form (frmSearchForm) that would open when I click on a button from the switchboard form.

This formSearchForm would give me the opportunity to enter a 3 digit ParticipantID and a button that I could click to "search/filter" another form called frmAthensQuestionnaire.

When I clicked search, I would like to have some code to check whether this is valid or not (i.e. whether this ParticipantID exists or not). If not valid it would return a message "NO records found". If valid, open my form frmAthensQuestionnaire with this number passed across as a filter/ WhereCondition.

Huvas, maybe try this:

on click (frmSearchFormButton)

  if DCOUNT("[ParticpantIDField]", "TheTableName", _
    "[ParticipantIDField] = Forms!frmSearchForm![I]IDcontrolName[/I]") > 0 then
    
      Docmd.openform "frmAthensQuestionnaire", acviewnormal, , _
        "[ParticipantID] = Forms!frmSearchForm![I]IDcontrolName[/I]"

  else
    msgbox "NO records found"

  end if

 

[ahuvas]

Hi Adam

Thanks for replying. However I am having trouble making it work. I get an error message about compile error/syntax error.

Here are the details:

My pop-up search form is called frmSearchForm
The text box where I want to enter the Participant ID (which is the primary key of the table underlying the form) is called txtSearch
THe table I want it to search is called tblParticipantData
and the form I want it to open in is called frmAthensQuestionnaire
The field that I want the txtSearch matched to in frmAthensQuestionnaire is called Participant ID.

 

[ajetrumpet]

Hi Adam

Thanks for replying. However I am having trouble making it work. I get an error message about compile error/syntax error.

Huvas,

I'm not surprised that you got syntax errors. Writing functions like these in Basic is different than if you write them in SQL (the quotation marks are placed differently, sometimes). The code I posted for you works fine for me, but I use 2007. I am starting to think that this version if a bit more lenient when reading these kinds of things. I was going to suggest dropping the brackets on around the field name in all 3 places, but from what you say, the field name has a space in it, correct? If it does, it needs surrounding brackets anyway, so you can't drop them. Maybe try this instead:

  if DCOUNT("[ParticpantIDField]", "TheTableName", _
    "[ParticipantIDField] = [COLOR="Red"]'" & Forms!frmSearchForm![I]IDcontrolName[/I] & "'"[/COLOR]) > 0 then
    
      Docmd.openform "frmAthensQuestionnaire", acviewnormal, , _
        "[ParticipantID] = Forms!frmSearchForm![I]IDcontrolName[/I]"

The original code, and the one posted above both work for me. Hopefully, one of them works for you! If not, can you post what ya got?

 

[ahuvas]

Sorry to be stupid

However where it says "TheTableName" "ParticipantIDField" and "IDControlName" do I change this to fit my situation i.e. replace the text THeTableName with tblParticipantData, "ParticipantIDField" with "ParticipantID" (since this is the name of the field in the frmmAthensQuestionnaire that is storing the data about the ParticipantID and replace IDCOntrolName with ?

Or its a purposeful code thing? and I should copy it exactly?

BTW The field that I want the txtSearch matched to in frmAthensQuestionnaire is actually called ParticipantID not Participant ID <-- this was a typo on here.

Here is the text I tried:

Private Sub btnSearch_Click()

If DCount("ParticpantID", "tblParticipantData", _
"ParticipantID = Forms!frmSearchForm!txtSearch") > 0 Then

DoCmd.OpenForm "frmAthensQuestionnaire", acViewNormal, , _
"ParticipantID = Forms!frmSearchForm!txtSearch"

Else
MsgBox "NO records found"

End If
End Sub

When I tried to put in my own field name I get this error:

Run Time Error 2471
The expression you entered as a query parameter produced this error 'ParticipantID'

Lastly, on the tblParticipantData/ParticipantIDfield in the frmAthensQuestionnaire the ParticipantID is formatted as a number. Does this affect how I format the txtSearch field or the quotation marks in the statement?

 

[ajetrumpet]

Sorry to be stupid

Hpwever where it says "TheTableName" "ParticipantIDField" and "IDControlName" do I change this to fit my situation i.e.

Hey, it's OK!

replace the text THeTableName with tblParticipantData

YES.

"ParticipantIDField" with "ParticipantID" (since this is the name of the field in the frmmAthensQuestionnaire that is storing the data about the ParticipantID

YES.

and replace IDCOntrolName with ?

With the name of the textbox that you are entering the input value to be tested.

 

[ahuvas]

Hi Adam while you were replying I changed my response there is some more info there

 

[ahuvas]

Here is the file

 

[ajetrumpet]

Lastly, on the tblParticipantData/ParticipantIDfield in the frmAthensQuestionnaire the ParticipantID is formatted as a number. Does this affect how I format the txtSearch field or the quotation marks in the statement?

The quotation marks in the DCOUNT function will not be changed because of formatting issues. The quotation marks serve as universal notation marks for the function itself. I think, when evaluating numeric values, strings are interchangeable with number formats, but you might try formating the textbox as a number, and trying it again.

But then again, the error message that you got is not related to the formatting issue. (looking at your file...)

 

[ahuvas]

This all started because I used to have a combo box form which would just filter the records to the participantID that they selected but one of the research assistants is as dumb as rocks and could not remember to do that before he started to try and enter data. You have to protect for stupidity sometimes

 

[ajetrumpet]

HUVAS,

In the first line of the DCOUNT function, you misspelled "Participant". You have "Particpant".

But the extra "i" in the word, and it will work fine. I just tried it.

and....what about stupidity again? Yeah, you got that code from my first post, so I'm the one that misspelled it. You have to protect against my stupidity too! So, watch out for programmers, and, especially accountants.

 

[ahuvas]

HAHAH DO you know how many days I have been working on this? HAHAH That's funny.

I looked so carefully too to make sure there were no errors. Its a trick of the mind when you see what you expect to see irregardless of if it is there or not. OkayI will post the final code so that if someone searches this forum they will be able to find the answer. The other problem is that I my knowledge of Access/VBA is enough to get me into trouble but not enough to get me out!

Here it is folks:

Private Sub btnSearch_Click()

If DCount("ParticipantID", "tblParticipantData", _
"ParticipantID = Forms!frmSearchForm!txtSearch") > 0 Then

DoCmd.OpenForm "frmAthensQuestionnaire", acViewNormal, , _
"ParticipantID = Forms!frmSearchForm!txtSearch"

Else
MsgBox "No records found"

End If

End Sub


Thank you - million ways over

If you are bored I have a question regarding if then else.

 

[ajetrumpet]

Thank you - million ways over

Welcome, a million times back.

If you are bored I have a question regarding if then else.

I'm at work, but that doesn't mean that I'm not bored. What is it?

 

[ahuvas]

Haha I wish I was at work. Its Sunday in Australia and I dont get paid enough as a PhD student to warrant it. I was supposed to be at the Australian Open (Tennis) today instead I am at my computer.

Okay there is a thread here regarding check-boxes.

Basically each question has 4 checkboxes (2 columns of two check boxes each) which can only be checked in three ways.

1. 2 boxes are checked in one coluimn only (either left column or right column)
2. One box is checked in each column
3. No boxes checked

I cant predict if the participant will check the 1st box in the left column first or the 2nd box in the left column etc even If I put instructions to do it a particular way and in the name of validation (read: protecting for studipity) I have to make sure that they dont break this rules.

I am able to Check Rule 1 i.e. If two boxes are ticked in one column, it causes the two boxes in the other column to become locked until the person unticks at least one of the boxes.

But I need a On Form close event that checks if the participant ticked at least one box in Question 1 i.e. in the left column that he/she has also ticked at least one box in the right column and vice versa, but this statement needs to be checked at least four times for each question (since there are three possible combinations of two boxes) and there are 8 questions. Im not sure how to place the else ifs, elses and endifs so that this is all checked as I close the form - if there is an error to report a message box, please check Question 1, Please check question 2 etc...

The initially code would look comething like:

On CLose Form

If Me.Q1L1 = -1 And (Me.Q1L2 = 0 and Me.Q1R1 = 0 and Me.Q1R2 = 0)
Then MsgBOx = "Please Check Question 1"

Else if Me.Q1L2 = -1 And (Me.Q1L1 = 0 and Me.Q1R1 = 0 and Me.Q1R2 = 0)
Then MsgBOx = "Please Check Question 1"


Else if Me.Q1R1 = -1 And (Me.Q1L1 = 0 and Me.Q1L2 = 0 and Me.Q1R2 = 0)
Then MsgBOx = "Please Check Question 1"


Else if Me.Q1R2 = -1 And (Me.Q1L1 = 0 and Me.Q1L2 = 0 and Me.Q1R1 = 0)
Then MsgBOx = "Please Check Question 1"

.
.
.
Repeated for each question, all checked on close form. I dont want it to stopped once it reached the first "error" like elseif statements often do because it wouldnt see any other errors in any other questions

 

[ajetrumpet]

Just wondering but, are you asking the same questions in this thread as you are in the "check box" thread, where Brian is helping you?

I only ask because getting advice from two different people in any field of work will confused the begeebers out of you. And, your post is hard to follow too. Is this new question related to the database that you have posted? If it is, just tell me what form you are referring to so I can look at while I read through your post.

 

[ahuvas]

Nope same thread but he didnt seem to answer my question regarding what to do on close form, just to use queries rather than invisible text field to produce text. When I meant "there is a thread here" in the post above I meant the threat that Brian was helping me with.

 

[ajetrumpet]

Well,

One thing you should know is that if you're trying to check any kind of conditional (with the OnClose event of the form) relating to a control on the form, it won't work, because the form has already started the closing process. Any conditionals regarding form values or objects have to be evaluated when the form is open. If it has already started closing, it's too late. A "close" button is a great way to prevent this type of thing from happening (just FYI on this one).

 

[ahuvas]

How does a close button that has the command to docmd.close form any different from closing the form?

 

[ajetrumpet]

The button is good because you can stop the closing process of the form if any condition is met. Say, for example, you have this code in the close event of the form:

if isnull(me.textbox) then
  [I]do not close the form[/I]

Too late, the triggered event is already closing the form.

But, if you have this:

Button On_Click

if isnull(me.textbox) then
  msgbox "you must fill in the textbox value before you can close the form!"
    EXIT SUB

else
  docmd.close

end if

With this, you can guarantee that a value be entered before the form is closed.

 

[ahuvas]

Are you able to help me on the otherthread organizing the else ifs and elseifs?