You are here:

Using MS Access/Inserting field values into a table

Advertisement


Question
QUESTION: I have two tables(tblemployee,tblloan) and one main form with a popup form. The popup form contains two fields one for the empID in a combo box and the other field is unbound. The two tables both have the field empID. The table tblloan has two fields empID and totalloan.  My problem is, I want to add the employee total loan into the tblloan from the popup form and ofcourse link the employee who is being added by comparing the adding empID and the existing tbloan.empID. I have this query that have been struggling with for days now that is not working ==> 'CurrentDb.Execute "INSERT INTO tblloan(totalloan)" & "VALUES(" & Me.txtTotalLoan & ")" & "WHERE Me.empID = Me.searchcbo2;"    txtTotalLoan is the name of the unbound loan field and searchcbo2 the combo box name.  Kindly help, am stuck and need your help.

ANSWER: A few things come to mind. First, is EmpID the primary key for tblLoan? Second, Is there a one to one relationship between tblEmployee and tblLoan?

The INSERT INTO isn't going to work, plus the code you are using will give you no message if EmpID is the PK or set to unique and the ID already exists. So you need to check if it already exists then either run an INSERT INTO if it doesn't or an UPDATE if it does.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: EmpID is the PK in the tblemployee table and a FK in the tblloan table.  There is a one-to-many relationship as an employee can have more than one loan from different banks.I thought I would be able to use INSERT INTO as shown and compare the combo selected item (EmpID) with the tblloan EmpID if similar then the employee's total loan shall be inserted in the totalloan field. Instead I am getting the message "Query input must contain at least one table or query." I just can't come out of this!

ANSWER: Ok, First, I would do this by having a subform on my employee form that lists loans. Then all you need to do is fill in the total loan amount for any new loan.

Another way I would do it is to have your popup form bound to tblLoan and open it in Add mode. Set the EmpID control to default to the EmpID from the main form. So when the popup form opens, the employee is shown. Then type in the Loan amount.

If you want to do this in a SQL statement, then use:

"INSERT INTO tblloan(EmpID, totalloan) VALUES(" & Me.searchcbo2 & ", " & Me.txtTotalLoan & ");"

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

my popup window
my popup window  
QUESTION: Hi Scott, I am asking too much but I seem to be getting somewhere. Please don't hold back, I need your help.

I am using the second option of having a popup window. After executing the INSERT statement as shown in your answer above, I am getting the message "Object required".  I just would like to select an employee ID from the combo  (like 45 shown)and then add the total loan amount in the tblloan. How do I set the EmpID control to default to the EmpID from the main form as you had said early?  Is it in the property sheet Data tab?

Answer
There should be a control on your form bound to the EmpID field. But you should also be able to use the combobox where you select an employee. As long as the Bound column for that combo is the EmpID column. The way to find the name of a control is to select it and check the Property Dialog box. The Name property is on the Other tab.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

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 25 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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.