Solved

ACCESS 2007 - VBA - Forms

Posted on 2011-09-09
8
216 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now