Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1561
  • Last Modified:

How to write If Statement in ACCESS 2003 VBA

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
premkripalani
Asked:
premkripalani
  • 5
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
premkripalaniAuthor Commented:
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
 
NizzeKCommented:
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
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'.

 
Jeffrey CoachmanMIS LiasonCommented:
NizzeK,

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

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
premkripalaniAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
premkripalani,

Try something like this for starters

JeffCoachman
AccessEEQ23474335SelectComboBoxU.mdb
0
 
premkripalaniAuthor Commented:
thanks.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now