Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to write If Statement in ACCESS 2003 VBA

Posted on 2008-06-10
9
Medium Priority
?
1,550 Views
Last Modified: 2013-11-28
Hi,
I have table call Facilities. I have 4 Facilities for each customer. Fac1, Fac2, Fac3 and Fac4.
I  want to create a New Form call FaciEdit.
I want a Combo Box for user to pick Fac1 to Fac4 and display related fields.
Could you please help me write simple VBA code or any other simple way to do this.
Thanks....
0
Comment
Question by:premkripalani
  • 5
  • 3
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21756510
premkripalani,

Create your form based on this table.
Drop in a combobox from the toolbox.
When the wizard opens, Select: "Find a record on my form..."
You should be able to finish the rest of the steps on your own.

This will make a dropdown box.
You can pick a Facilty, and it will be displayed on the form.

Any question, post back here.

JeffCoachman
0
 

Author Comment

by:premkripalani
ID: 21756700
Thanks, but its not that simple.
Facility combo box is unbound. There is no facility No. field in the table.
My table looks like this
F1Cust_no
F1Cust_name
F1Address
F2Cust_no
F2Cust_name
F2Address    
What I want to do is when user select Facility 1 in combo box
it should display F1Cust_no,F1Cust_name,F1Address fields on the screen
and if user select Facility 2 in combo box it should display F2 fields
Please help....thanks.
0
 
LVL 3

Expert Comment

by:NizzeK
ID: 21756902
Hi!
The attached code will take the value from comboFacilitySelect upon change and open a form window that can be identical in design for all 4 facilities, but different field links. The combo must be loaded with the numbers 1-4, or you need to replace with the actual choices at each case statement.
If this isn't what you wanted, I wuld apreciate a piece of your database to try to implement your wish.

Best regards
Nils

sub comboFacilitySelect_Change()	
    select case comboFacilitySelect.value
	case 1: DoCmd.OpenForm "frmFacil1", acNormal, , , acFormEdit
	case 2: DoCmd.OpenForm "frmFacil2", acNormal, , , acFormEdit
	case 3: DoCmd.OpenForm "frmFacil3", acNormal, , , acFormEdit
	case 4: DoCmd.OpenForm "frmFacil4", acNormal, , , acFormEdit
	case else:  'Here you can have: Msgbox "Invalid selection"
    end select
end sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21756948
NizzeK,

The real issue here is that the data is not structured properly.
Let's try to fix the structure first.
;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21756955
premkripalani,

<Thanks, but its not that simple.>
Well, it should be.
;-)

Your design is not normalized, and is a very bad way to store this type of date.
I urge you to rethink this design

(How did you come to design your table in this way?)

You should have two tables: tblCustomers and tblFacilities
They will be linked on Cust_no, and referential integrity enforced.
(One Customer, Many facilities)

Then all you would need to do is make a Mainform (Customers) and a subform (Facilities) with the wizard.
Then use the combobox wizard to create a search for the customer.

Then all you would have to do is select a customer from the combobox, and all 4 facilities would appear in the subform, ready for you to edit.

Here is a sample
(This took me about 5 minutes to make.)

JeffCoachman

AccessEEQ23474335OneToManyRelati.mdb
0
 

Author Comment

by:premkripalani
ID: 21765039
Hi, Thanks a lot for your solution and comments....
I do understand normalisation and all. However, I am not the developer or designer of this database. I dont have much time and choice otherwise i wud have started from scratch.
My problem is both Customer and Facility table is One on One.
In Facility talbes i have four facilities.
Each facility has 10 fields.
Can i write VBA code with If statement ?????
If Combobox = "Facility 1" , Display = "Fac1Name","Fac1Address"..... and so on....
Please help.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 total points
ID: 21765561
premkripalani,

Try something like this for starters

JeffCoachman
AccessEEQ23474335SelectComboBoxU.mdb
0
 

Author Closing Comment

by:premkripalani
ID: 31465992
thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21798894
;-)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

824 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