Solved

Check number in database

Posted on 2013-12-20
12
274 Views
Last Modified: 2013-12-20
Hi -

I use Access 2010.

I have two tables and one form.   The main record source for the form points to table1_Main, however the drop down menu I have in the form is looking at another table called table2_Contacts.  I want to make sure then when a user adds a new record and selects a ContactID from the drop down menu that it can only be added to the database once.  If someone tries adding a new record and selects the same ContactID it will inform them that this ContactID has already been used already and can't be used again.

Hope this makes sense.

thanks.
0
Comment
Question by:CptPicard
12 Comments
 
LVL 6

Expert Comment

by:RaithZ
ID: 39731759
It would help to know the layout of your table that contains the contact ID but you could use a select with the having clause to count how many times that contact ID appears and then return a message if it already exists.. but that would have to be executed before the insert into the table, most likley a macro.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39731789
use the beforeUpdate event of the combo box and use the dcount() function

private sub comboName_beforeupdate(cancel as integer)

if dcount("*","table2_Contacts", "contactID=" & me.comboName) > 0 then
   msgbox "Contact ID already exists!"
   cancel=true
end if


end sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39731809
One way to do this is to modify the query for the dropdown so that it already filters out those

Instead of something like:

Select ContactID, ContactName from table2_Contacts Order by ContactName

as the RowSource for your dropdown, use something like:

SELECT ContactID, ContactName
FROM table2_Contacts
LEFT JOIN  table1_Main
ON table2_Contacts.ContactID = table1_Main.ContactID
WHERE table1_Main.ID IS NULL
ORDER BY table_2.ContactName

This would filter out all of the used contacts prior to even populating the drop-down.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Expert Comment

by:hnasr
ID: 39731814
Try to upload a sample database showing the issue.

You may check for the ContactID in the after update of the drop down list.
0
 

Author Closing Comment

by:CptPicard
ID: 39731827
This works perfectly.  Thank you.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39731880
Were you trying to check whether that contact had already been assigned to a record in the main form?  If so, then you are going to need to use:

if dcount("*","table1_Main", "contactID=" & me.comboName) > 0 then
instead of:
if dcount("*","table2_Contacts", "contactID=" & me.comboName) > 0 then

And if that isn't what you are trying to do, then I don't know how Capricorn1's solution will prevent reuse of that ContactID.  I think it would prevent use of any ContactID!
0
 

Author Comment

by:CptPicard
ID: 39732026
Well spotted!  I did indeed notice this and changed it myself because as you say, it would prevent the use of any ContactID.
0
 

Author Comment

by:CptPicard
ID: 39732151
This all works fine but I've just realized that I have a search form as well.  Basically this is used to display a list of Contacts with specific information about them. Contact ID, Title, Forename, Surname etc...  You can simply search for them within the form, click on them, and click a command button to input their info to the main form (Saves typing it in manually).

The Contacts within the search form are displayed within an unbound listbox with the row source being the Contacts table.  When you click the command button 'Input to form', it executes the following 'on click' event procedure.

Private Sub Command25_Click()

On Error GoTo Err_Command25_DblClick

Forms("table1_main")![cboContact_ID] = ListBox.Column(0)
Forms("table1_main")![cboTitle] = ListBox.Column(1)
Forms("table1_main")![cboForename] = ListBox.Column(2)
Forms("table1_main")![cboSurname] = ListBox.Column(3)
Forms("table1_main")![cboDOB] = ListBox.Column(4)
Forms("table1_main")![cboGender] = ListBox.Column(5)
Forms("table1_main")![cboTelephone] = ListBox.Column(6)
Me.Refresh

DoCmd.Close

If IsNull(ListBox.Value) Then

DoCmd.Close
DoCmd.OpenForm ("table1_main")

End If

Exit_Command25_DblClick:
    Exit Sub

Err_Command25_DblClick:
   
    Resume Exit_Command25_DblClick

End Sub


I'm not sure now how I can check if their ContactID has been entered into the system if I'm using this search form.  It's easy from the drop down menu by putting the code in the before update, but not sure what's best when you're using another form and sending the information to your main form.

I'm happy to open another question if it needs it.

Cheers.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39732167
Why are you storing all of that information in table1_Main?  

The only field you should store in that table is ContactID.  You should use that field either in a query to capture the other info from table2_Contacts, or create a subform on form_Main to display the contact data linked on the ContactID field.
0
 

Author Comment

by:CptPicard
ID: 39732560
The database is a little more complicated than that but I'm trying to simplify what I'm asking so as to not over complicate things.

I actually have 4 tables and 4 forms.  One of the tables stores all the contacts details. the other tables are called tbl_questions1, tbl_questions2, tbl_questions3.  

Forms are called frm_questions1, frm_questions2, frm_questions3, frm_searchform.

Within frm_questions1 for example, there is a command button which you click on to open up the 'frm_searchform'. Within this form you can search through hundreds of people to find the one you want to input into your main form 'frm_questions1'.  It allows you to search for their name, click on their line within the listbox and click a button to input their details into the 'frm_questions1' form.  All their details automatically get put into the relevant fields automatically.

My original question asked how I checked to see if the ContactID  had already been entered into the database 'tbl_questions1' and capricorn1 came up with the perfect answer.  This allowed me to select a ContactID and if it had already been entered into the database, it would display a message.  If it had not already been entered into the database it would not display a message and their Contact Details would automatically populate the fields.  However, it's a little more complicated when you open up a search form and try and do the same thing.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39732600
My point is that you do not (actually should not) duplicate all of the fields from tbl_Contacts into your other tables.  All you should store from that table in the other tables is the ContactID field.  The rest of the information belonging to that contactID can be displayed either via a query, or using a subform.

So, from your search form, the button "command25_Click" (BTW, you really need to institute a naming convention for your controls.  You should initially check to see whether that contact ID already exists in that forms recordset.  The challenge is that it appears that you may have more than one recordset to check.  I'm also confused by the fact that you are using the Command25_Click event to insert values into fields (controls I suspect) on form1_Main, and then after you do that, you test to see if form1 is open.  How do you do that.  At any rate, to test to see whether that ID is already in use, you would basically use the same code provided above:

     strCriteria = "[ContactID] = " & me
    if dcount("*","table1_Main", strCriteria) > 0 then
        msgbox "Contact ID is already in use!"
        Exit sub
    End If

do other stuff here.
0
 

Author Comment

by:CptPicard
ID: 39732622
Tried the above but it gives me a type mismatch on this section

strCriteria = "[Patient_Num] = " & Me
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 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