Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Open another form and show same unique ID

Posted on 2013-12-04
15
472 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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