Solved

Update "unlinked" table

Posted on 2009-04-01
9
587 Views
Last Modified: 2013-11-27
I have an HR Database, contained within it is a form to enter supplier contact details (i.e. recruitment firms) [frmrectoreccontacts].  The name of the field that holds the supplier company name is [Company].  

Due to the set up of the database it is fairly clunky and basic but there is a link in an employees record to show who supplied them.  This links to a sub form that, on the supplier page, shows who they have introduced to us.  

When I am entering a new employee there is a drop down box showing a list of recruitment firms for me to select who supplied them from a control source [tblWhoIntroduced].  If the supplier isnt listed in the drop down then I need to go to the [frmrectoreccontacts] to enter a new supplier. The trouble is that the new supplier isnt linked to the drop down box in the employees record as this is formed from a separate table [tblWhoIntroduced].    

I would like to produce a command button which sits in the supplier record [frmrectoreccontacts] and copies the supplier company name [Company] and adds that to the table that forms the drop down box [tblWhoIntroduced]. It would then refresh the dropdown box in the employees record [frmResearchAllDetailsMainPage]

Can anyone help - is this possible?

Many thanks

Tom
0
Comment
Question by:correlate
[X]
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
  • 4
  • 2
9 Comments
 
LVL 12

Accepted Solution

by:
koutny earned 500 total points
ID: 24058043
I don't know whether you are still looking for a solution for this but adding a button to the form is an easy thing.
However, I think you have a problem with the design of the db. Why is there the table [tblWhoIntroduced] at all? Why is the field in the emplyees table not linked directly to the supplier table?

You are introducing duplicity into the database and it will be extremely difficult for you the maintain the db. For example what if you discover that you made a mistake while entering the name of the supplier. How will you update the data in the other table?
 So I would strongly suggest that you rethink the design of the tables and try to make them conform to database normalisation rules.

To answer your question though, the code for the button in the suppliers form would be something like the code below. (There is another problem with this design - what if there are two companies with the same name?)


Private Sub cmdAddCompanyName_Click()
Dim strSQL As String
    On Error GoTo err_label
    strSQL = "INSERT INTO tblWhoIntroduced(Company) VALUES (""" & Me!Company & """" & ")"
    CurrentDb.Execute strSQL
    Exit Sub
err_label:
    MsgBox "Error adding supplier name" & vbNewLine & Err.Description, vbCritical, "Error adding a new record"
End Sub

Open in new window

0
 

Author Comment

by:correlate
ID: 24058129
Superb - work beautifully so thanks for that - I know this is not the most efficient way of doing it, but I'm not really technically competetant enough to be able to redesign the whole thing, as large parts of it were designed by someone else.  

As a side note - is there a way to do the reverse, i.e. delete the record from that table ?
0
 
LVL 12

Expert Comment

by:koutny
ID: 24058262
Sure you can delete records via code. You might want to paste this in the afterdelete event handler of the suppliers form:


Private Sub Form_Delete(Cancel As Integer)
Dim strSQL As String
    On Error GoTo err_label
 
    strSQL = "DELETE FROM tblWhoIntroduced WHERE Company = """ & Me!Company & """"
    CurrentDb.Execute strSQL, dbSeeChanges
    
    Exit Sub
err_label:
    MsgBox "Error removing supplier name" & vbNewLine & Err.Description, vbCritical, "Error removing record"
End Sub

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:koutny
ID: 24058267
sorry, I meant the Delete event, not After delete.
0
 

Author Closing Comment

by:correlate
ID: 31565213
Spot on perfect - thank you very much & Also for the deletion piece
0
 
LVL 12

Expert Comment

by:koutny
ID: 24058393
You are welcome, I am glad I could be of some help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

734 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