Solved

Open another form and show same unique ID

Posted on 2013-12-04
15
456 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now