UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Check If Record Exists..., Access 2010    
 
   
BShick5088
post Jan 6 2017, 10:20 AM
Post#1



Posts: 48
Joined: 15-January 11



Good morning,
I am having trouble getting my mind around this... We have a DB where a project folder can be house more than one project and need to create code to check if the combination of project and folder already exists. Here is what I am trying to use but doesn't work...

If Me!cboAddFolderNo And Me.cboAddProjectNo = DLookup("[FolderNo]", "[tblMVPDocData]", "[FolderNo]='" & Me!cboAddFolderNo & "'") And Me!cboAddProjectNo = DLookup("[ProjectNo]", "[tblMVPDocData]", "[ProjectNo]='" & Me!cboAddProjectNo & "'") Then
MsgBox "A Project with this Folder number already exists."
Me.cboAddProjectNo.SetFocus
Exit Sub
End If

Am I on the right track? Or is there a better way to do this?

Thanks for any help...
Brian
Go to the top of the page
 
ScottGem
post Jan 6 2017, 10:26 AM
Post#2


UtterAccess VIP / UA Clown
Posts: 32,185
Joined: 21-January 04
From: LI, NY


What is the primary key tblMVPDocData? What is the Rowsource and Bound columns for the 2 comboboxes for folder and project?

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
River59
post Jan 6 2017, 10:33 AM
Post#3



Posts: 1,348
Joined: 7-April 10
From: Detroit, MI


This is untested but you might want to try something more along the lines of ...

If Not IsNull(DLookup("[FolderNo]", "[tblMVPDocData]", "[FolderNo]='" & Me!cboAddFolderNo & "' And [ProjectNo]='" & Me!cboAddProjectNo & "'") Then
MsgBox "A Project with this Folder number already exists."
Me.cboAddProjectNo.SetFocus
Exit Sub
End If

Didn't realize Scott was responding at the same time.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
doctor9
post Jan 6 2017, 10:34 AM
Post#4


UtterAccess Editor
Posts: 17,926
Joined: 29-March 05
From: Wisconsin


Brian,

It depends on your table structure for tblMVPDocData and whether you have separate tables for folders and projects and how those tables are set up.

If you have project folders and you have projects, ideally you should have tables for folders and projects, and your tblMVPDocData table should have foreign key fields that link to those tables. Based on your current code, I'm not sure you've got that setup, because you're searching for the text of the folder number, rather than the primary key value. Do you have tables listing the projects and folders? If so, you should probably set up cboAddFolderNo and cboAddProjectNo to use the primary key fields from those tables rather than just the text values, as that can simplify things quite a bit, including reducing any problems with apostrophes in your folder/project names.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
BShick5088
post Jan 6 2017, 11:10 AM
Post#5



Posts: 48
Joined: 15-January 11



Thank you all so much for responding so quickly...!

Yes I have 2 tables in a back-end DB, one is tblMVPFolderNo and the other MVPDocData. The tblMVPFolderNo only has one field column FolderNo that is a Primary Key. That field is related to the tblMVPDocData to a FolderNo field with a "One to Many" relationship. This allows the user to maintain one folder number but can have many projects. The problem is that through the existing code it is possible to enter duplicate projects under that folder number. This is caused by the fact that a project can be spread across multiple folders so I can't use the ProjectNo as a Primary Key.

To tell the truth I have never heard of a Foreign Key... Is that like a Primary Key? Where is the icon on the ribbon?

I will try using the code given in an earlier response and let you all know if it works.

Thanks again for your speedy responses and giving of your time.
Brian
Go to the top of the page
 
ScottGem
post Jan 6 2017, 11:19 AM
Post#6


UtterAccess VIP / UA Clown
Posts: 32,185
Joined: 21-January 04
From: LI, NY


A Foreign Key is the field in a child table where you store the value of the keyfield in the parent table. So you have a table of foldernames and then a foreign key to indicate the folder that Doc is stored in. An FK is defined by its usage and the only requirement is that it be the same datatype as the parent key.

You should also have a projects table with ProjectID as a FK in tblMVPDocData. tblMVPDocData should also have its own PK.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
BShick5088
post Jan 6 2017, 11:28 AM
Post#7



Posts: 48
Joined: 15-January 11



I just ran a test on a project using the code above and it works great!
I only needed to add a ) at the end.
You guys are so cool!

If Not IsNull(DLookup("[FolderNo]", "[tblMVPDocData]", "[FolderNo]='" & Me!cboAddFolderNo & "' And [ProjectNo]='" & Me!cboAddProjectNo & "'")) Then

Thank you all again!!!
Go to the top of the page
 
River59
post Jan 6 2017, 11:43 AM
Post#8



Posts: 1,348
Joined: 7-April 10
From: Detroit, MI


Nice catch on the last parenthesis ...

Glad you got it working.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 11:43 AM