ACCESS 2007 - VBA - Forms

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.
shmzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
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
 
peter57rCommented:
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
 
shmzAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
shmzAuthor Commented:
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
 
peter57rCommented:
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
 
shmzAuthor Commented:
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
 
shmzAuthor Commented:
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
 
shmzAuthor Commented:
Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.