Solved

How to write If Statement in ACCESS 2003 VBA

Posted on 2008-06-10
9
1,523 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

831 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