[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How Do I Open A Form To A Specific Record?

Posted on 2011-04-25
9
Medium Priority
?
512 Views
Last Modified: 2013-11-28
Hi experts.

I've got a form which I want to open and filter the form to a "ASGeneralWebsiteRegID". The article table (the one being opened) may not have a ASGeneralWebsiteRegID, but using the cascade update relationship, I presumed since this is opening on filter, it would automatically insert the filtered ASGeneralWebsiteRegID for every new article record.

I've attached a document with screen shots below to shed further light. I'm not sure why the article table appears to open up on filter, but on closer inspection, you see it has "0" and not linking?

Could you please advise how I can open up the article form, using the Registration form's current ASGeneralWebsiteRegID and therefore every new article added would automatically add the current filtered ASGeneralWebsiteRegID?

Thanks,
J   Form-Open-Filter-Problem-260411.doc
0
Comment
Question by:jammin140900
  • 4
  • 2
  • 2
  • +1
9 Comments
 

Author Comment

by:jammin140900
ID: 35464502
Had to make a small change in the last hour. I now have WebsiteID from tblWebsite and tblASArticle in a relationship (Cascade update and Delete). Changed the Article Form and the Open Form button on the Regstration form to reflect this change.

Still doesn't open the Article Form using the current Registration Form's Website ID.
0
 
LVL 85
ID: 35465776
Your code is correct, assuming you're properly picking up the ASGeneralWebSiteRegID (or whatever field you're using). Does your query properly filter those results? That is, if you open the query in Design view and place the value 55 in the Filter row for that field, do you then see the correct record(s) show in your query?

If you don't, then you need to get the query right. The form can only show those records as provided by the query.

<but using the cascade update relationship>

Cascade Update has no bearing on this issue, and in fact is a setting that you should NOT be using in a properly structured database. Cascade update simply updates any values in child tables IF the Primary Key of the parent table changes. IN a well designed system it is very, very rare that this will ever happen.
0
 

Author Comment

by:jammin140900
ID: 35466348
I checked the filter and it is correct.

Thanks for the explanation for the cascade update. I forgot the purpose for that (not having to build a database in ages!)

So, lets say you have a table called Product and another table called Comment. Product contained Product ID, Product Name (and so on..) and Comment contained Comment ID, Comment and Product ID.

Let's say you had two forms, one for each table. Comment was in datasheet form. You also had a button on the Product Form that allowed you to open the Comment Form on the current Product ID. Obviously the Product ID may not exist in the Comment form as yet.

How do you insert the Product ID in the comment form so that it would open correctly and secondly, then filter the comment form so that everytime you entered a new comment for that same product, the ProductID would add itself in the comments table? Is it perhaps a table property that requires changing in the Comments table or the establishing of a relationship?



0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 800 total points
ID: 35467780
You'd need to pass the ProductID into the Comment form, and use code to insure that it's inserted correctly. You can do that with several methods, but basically you need to get the ProductID whenever you're adding a New comment. YOu can use the Current event of the Comment form to determine this:

Sub Form_Current()
  If Me.NewRecord Then
    Me.ProductID = <YourProductID>
  End If
End Sub

You can get the ProductID with several methods:

1) Use the OpenArgs method to pass in the ProductID, and use that if you add a new record. Store the value in a form-level variable, or in a hidden control

2) Grab the value when the user adds a new record. If this form is ONLY used to add comments to products, you can refer to the open Products form like this:

Forms("YOurProductForm").YourProductID

3) Use a global variable technique that insures the value is availabe throughout the program (not recommended, at least by me).
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 200 total points
ID: 35468487
Another method:  save the ProductID to a custom database property, and use it as needed for filtering in queries.  This method preserves the value even if the database is closed and reopened.  The advantage (for working with queries) is that there is no need to have a specific form open to work on the query.  Here is some code for working with custom database properties:
Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strPropertyValue As String
Private lngDataType as Long
Private varPropertyValue As Variant


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set dbs = CurrentDb
   Set prps = dbs.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

Date
====
   strPropertyName = "PropName"
   lngDataType = dbDate
   Call SetProperty(strPropertyName, lngDataType, dteStart)

   GetStartDate = CDate(GetProperty("PropName", ""))

Text
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)

   lngID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, intMonth)

   intID = CInt(GetProperty("PropName", ""))

Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:jammin140900
ID: 35472764
Thanks guys. The theory makes sense. Just not very confident in applying it just yet!

I've attached a sample database.

Ok. When you open the database, on the frmWebsite, using the 'Select Website', select "learntosing.com"

Click on the red button "Article Submitter Registration"
Click on the red button "Go To Article Details" at the bottom

and now you open the form ASArticleDetails. You'll notice that it's come in filtered on WebsiteID = 11.

So, when I click on "Add New Article", how do I keep the WebsiteID = 11 for every new record?


 Sample-Database.mdb
0
 
LVL 7

Accepted Solution

by:
manthanein earned 1000 total points
ID: 35473129
you could   set  the  defaultValue of   WebsiteID  textbox on  frmASArticle  to  =[Forms]![frmASRegistration]![WebsiteID]
0
 

Author Comment

by:jammin140900
ID: 35474236
Brilliant Manthanien.. So simple but works a treat! Thank you!
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35474960
You can also set the value of a textbox on the form from a saved database property -- I use this technique on a main menu.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

834 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