Solved

ACCESS 2007 - VBA - Forms

Posted on 2011-09-09
8
217 Views
Last Modified: 2012-05-12
Hi,

I have an application in Access 2007.  The form were related to non related tables in sql. I have created a relational db in sql and as a result I made changes to database columns and adding fk's....

I have problem with the form interface. I am not familiar with access but here is the problem I have:
The 'FORM' has a control source query in its control source, it is for example: select * from employee.

Then there are drop down boxes, which they also have their query.I select values from the dropdownbox and they wil be inerted into employee table. upon pressing enter key.

The form WAS deigned so all fields in the form had the same name of employee table columns, where information is suppose to be stored.
 but now it is different. now instead of having salaryRange in employee table, I actually have only SalaryRangeID as a Foreign key in employee table.

if I select SalaryRange from salary dropdown box (which is populated using select SalaryRange from tblSalaryRange), this field (SalaryRange column) doesn't exist in employee table anymore and form generates error. (#NAME?)

how can I solve this problem.
Someone mentioned use of VIEW but I have no idea how that can be done.

Many Thanks in advance.
0
Comment
Question by:shmz
  • 5
  • 3
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36515465
For the Salary dropdown you need to change the structure of it by modifying some of its properties.

For the rowsource , you need two columns, salaryrangeid and salaryrange. Select them in this order, so that the rowsource looks something like..
Select salaryrangeid, salaryrange from salaryrangetable

Change the number of columns property to 2

In the Column widths property set the width of the first column to zero, so that the salaryrangeid does not show in the dropdown.
The column widths property will then look something like...
0cm;1cm

The bound column property should currently be set to 1 and this stays as 1.

The controlsource for the combo should be SalaryRangeID

The combo should now display the salaryrange values for selection by the user but the value stored in the table will be the bound column- salaryrangeid.
0
 

Author Comment

by:shmz
ID: 36517669
Thank yo uso much, I could get it working.

In the same form I have another dropdown box which behave differently.

I have a payment comboBox. So query is: select paymentID, paymentMethod from tablePayment.
PaymentID is bound to the combobox, as well as being displayed in the box. There is textfield  under combobox, called payment Method.

(the payment method column does not exist in the employee table, I assume it is just for display). Everytime I populate the paymentID combobox, the NAme#? error being displayed in paymentMethod textfield.

here are the info:
ParymentID
Data:control source: paymentID
          row source: select paymentID, paymentMethod from tablePayment
          bound column:1
Event: After Update
Other:cmb_Payment
NAme:cmb_payment (name)
            paymentid (source control)

payMethod:        
Data: PayMethod (plaintext)
Other: Name :PayMethod
All: Paymethod
     Source control: PayMethod

In the code behind I have:
cmb_payment|select paymentID, payMthod from paymenttable;

and also
Sub...cmb_payment_AfterUpdate()
Me![payMethod] = Me!cmb_payment.column(1)

How can I get this work?

Thank you so much for your help. I can ask my questions in different posting if you prefer. (just didn't want to loose you...

Many thanks in advance

0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 36518271
There is textfield  under combobox, called payment Method.

If you are seeing a Name error in this box then you are trying to link it to a field that does not exist.
Check the controlsource - it should be empty.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:shmz
ID: 36526671
I left control source empty and it is being displayed correctly. the only problem is that when I do data entry, and form goes blank, these text fields still hold the name.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36527586
Yes, that's how unbound controls work.You have to use code to clear them.

In your form curent event use...

if not isnull(Me!cmb_payment) then
Me![payMethod] = Me!cmb_payment.column(1)
else
Me![payMethod] =""
end if

In fact you should probably change your existing code to the same, just in case the user clears the current combo box value.
0
 

Author Comment

by:shmz
ID: 36533405
It did not work.
The problem is that the form has many subforms. some of the fields (including method_payment) are on the form. after selecting methodPayment, (This field does populate correctly when I change the selection), I press tab and form gets submitted to sql table and it should return this item that is just added to the database back into form so that can populate subform for it. this feature is not curently working. (I issue a new ticket for this as it seems pretty serious) I am not sure if it is related to this problem?
0
 

Author Comment

by:shmz
ID: 36533541
I appreciate if you could look at this question as well:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27306726.html

Many Thanks
0
 

Author Closing Comment

by:shmz
ID: 36540427
Many Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Is there any way to convert exponential value to number in sql server 5 38
Access on Mouse move 5 30
Create report using crosstab query 11 29
Reference Controls on subforms 7 27
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

786 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