AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Expert: Scottgem Date: 7/18/2008 Subject: stLinkCriteria record pull up
Question QUESTION: Hey Scott,
I am trying to make my interface so that it will pull up the same unique record when I jump from form to form in my case a catalog number. I tried doing this via the MS access default way with stLinkCriteria however there are some instance where null values can mess this up. Is there another way to do this???
Thanks a million
Joshua
ANSWER: Not really. Any way you try to open a form to a specific record will blow up if there is a null value. What you want to do is trap for the null using an IF statement. For example:
IF IsNull(Me.txtCatalogNumber) Then
MsgBox "Catalong Number is blank!", vbOKOnly
Else
DoCmd.OpenForm "formname",,,"[CatalongNumber] = " & Me.txtCatalogNumber
End If
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: MS Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: ok thanks that question has been eating at me for a while. Im looking at the short code you gave me and I wondered how you code this if you were going from a form that has a unique catalog number and opening a form that has a null value for the same unique catalog number. For instance, UniquieID= 60, i go from tblLocation -----> (to) tblDescription.......
however tblDescription has a null value for 60. How do I work around this using the If statement?
Thanks,
Joshua
Answer Are you saying that you want to open a form filtering for a catalog items that isn't in the recordsource for that form? then use a Dcount:
If DCount("*", "tablename", "[CatalogNumber] = " & Me.txtCatalogNumber) = 0 Then
MsgBox, No matching catalog entries.
Else
Docmd.Openform...
End If
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
