Solved

ACCESS 2007 - VBA - Forms

Posted on 2011-09-09
8
215 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now