Solved

Open another form and show same unique ID

Posted on 2013-12-04
15
441 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What is the recordsource of the second form?
0
 

Author Comment

by:CptPicard
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:CptPicard
Comment Utility
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
Comment Utility
<<
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
Comment Utility
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
Comment Utility
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
Comment Utility
All works, thank you.
0
 

Author Comment

by:CptPicard
Comment Utility
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
Comment Utility
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
Comment Utility
thank you.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

16 Experts available now in Live!

Get 1:1 Help Now