Solved

How to write If Statement in ACCESS 2003 VBA

Posted on 2008-06-10
9
1,524 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 250 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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