Solved

Open another form and show same unique ID

Posted on 2013-12-04
15
481 Views
Last Modified: 2013-12-04
Hello - I use Microsoft Access 2010 and I have two tables (tbl_personaldetails & tbl_questions) I also have to forms (frm_questions1 & frm_questions2).

Both forms share a unique number called 'PersonalID'.  This does not use the Primary Key and it is related to this particular person.

I have a button on both my forms called 'View Question 1 Responses' & View Question Two Responses'.  

Basically, there are two forms for two sets of questions.  The first form with the first set of questions has 15 questions and the second form has about 10.   The second form which shows the second questions are basically a follow up from the first set of questions to see if they've improved since they were first asked.

I need a way for the user to click the button 'View Question 2 Responses' and it opens up the second form 'frm_questions2' and takes them to the same person by their unique ID which uses the field 'PersonalID'.

Ideally, It'd work best that the button wasn't available unless there were questions answered in the second form from this particular person.  If they've not answered the questions in the second form yet, then the button could either be grayed out or invisible.  If there were questions answered, then it would be visible and the user could click on it.  It would be used for a quick way to compare their answers from both forms.

Hope you can help.
0
Comment
Question by:CptPicard
  • 8
  • 7
15 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39696057
In a command button click event on the first form (assuming numeric ID):
Docmd.OpenForm "'frm_questions2", WherCondition := "PersonalID = " & Me.PersonalID

Open in new window


If text:

Docmd.OpenForm "'frm_questions2", WherCondition := "PersonalID = '" & Me.PersonalID & "'"

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39696077
Also, to hide/show your button, you could do something like this -- add the following code to the form's Current Event:

Me.cmdButton,Visible = DCount("*", "tbl_questions", "PersonalID = " & Me.PersonalID)

Open in new window


That looks for questions associated with the person's ID, but you would probably have to add criteria to determine which set of questions.
0
 

Author Comment

by:CptPicard
ID: 39696088
Hi Mbizup

That opens the form and displays the same PersonalID (I worked this out already). But what happens is it filters it so that if I tried going to the next record in this form, it can't find any.  I may want to look at other questions and not just this one I've opened using the button.

Also, can you advise, as mentioned above, on how the button is visible or invisible depending on whether there's data for this PersonalID in the second form.  I don't want a user clicking this button in the first form if there's no data for the person in the second form.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CptPicard
ID: 39696103
Don't need to add any criteria, it just needs to see if PersonalID has been used in the second form.  If it has, then the button should be visible.  If the PersonalID hasn't been used in the second form, then it will be invisible or grayed out.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 39696107
Ok... so you just want to move to a record, with all displayed?

dim rs as dao.recordset
Docmd.OpenForm "frm_questions2"
With forms("frm_questions2")
      set rs = .RecordsetClone
      rs.FindFirst "PersonalID = " & Me.PersonalID
      if rs.NoMatch = true then
             msgbox "No Match" 
             set rs = nothing
             exit sub
      end if
       .Bookmark = rs.Bookmark
end with
set rs = nothing

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39696119
What is the recordsource of the second form?
0
 

Author Comment

by:CptPicard
ID: 39696133
That nearly works.  It opens the form even if there's no match.  Would be better to not open the form at all if there's no match if possible.  Also, can't seem to get this to work

Me.cmdButton,Visible = DCount("*", "tbl_questions", "PersonalID = " & Me.PersonalID)

I get a compile error:
Invalid use of property.
0
 

Author Comment

by:CptPicard
ID: 39696135
record source for the second form is is my table called tbl_questions2.  Sorry, I have three tables :(
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39696152
<<
Would be better to not open the form at all if there's no match if possible.  Also, can't seem to get this to work
>>

That's why we're hiding the button, right? :)   (And why I'm asking about criteria/recordsource for the second form)

My syntax was off (I used a comma where I should have used a dot), but try this in your first form's current event:

Me.cmdButton,Visible = (DCount("*", "tbl_questions2", "PersonalID = " & Me.PersonalID) > 0)

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39696157
Darn it!   I did it again... used a comma.

Corrected:

Me.cmdButton.Visible = (DCount("*", "tbl_questions2", "PersonalID = " & Me.PersonalID) > 0)

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39696161
As an aside, you could have used a single table for this with an additional field named something like "QuestionSet" to distinguish which form the questions appear on.
0
 

Author Comment

by:CptPicard
ID: 39696810
All works, thank you.
0
 

Author Comment

by:CptPicard
ID: 39696887
Just noticed a bug actually.  If I click on the next record and it's empty, I get a run time error '3075' Syntax error (missing operator) in query expression 'PersonalID = '.    Which is to do with this line

Me.cmdButton.Visible = (DCount("*", "tbl_questions2", "PersonalID = " & Me.PersonalID) > 0)

Sorry, I thought it was all ok.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39696924
Try this:

Me.cmdButton.Visible = (DCount("*", "tbl_questions2", "PersonalID = " & NZ(Me.PersonalID, -1)) > 0)

Open in new window


That just detects nulls, and substitutes a -1  (an arbitrary number which in theory will not be in your data)

Let me know if that works for you
0
 

Author Comment

by:CptPicard
ID: 39696951
thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question