?
Solved

How to write If Statement in ACCESS 2003 VBA

Posted on 2008-06-10
9
Medium Priority
?
1,538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

719 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