UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open Form Based On Cbo    
 
   
Aakburns
post Feb 5 2020, 04:35 PM
Post#1



Posts: 17
Joined: 4-December 19



I have a dataview sheet that I use here. One of the columns in the table give 3 options to choose from in a combo box. When you double click on the 'Job_ID' it opens a form to edit the fields for this the selected job. This opens based on the job_id in the row.

What I need is the 'Job Type' depending on what is selected in the 3 options to open between 3 different forms when double clicking the job_id.
Currently I just use an expression to open a form for all 3, but it opens the same form. Is there a simple way in VBA to go about getting this to refence the Job Type combo box to open the correct form?
Attached File(s)
Attached File  Annotation_2020_02_05_153050.png ( 11.99K )Number of downloads: 8
Attached File  Annotation_2020_02_05_153501.png ( 9.42K )Number of downloads: 5
 
Go to the top of the page
 
BentBrain
post Feb 7 2020, 12:59 AM
Post#2



Posts: 517
Joined: 10-February 03
From: Thailand


Hi Aakburns
Yes, you can do want you want by using an DblClick event procedure instead of a macro

try this with - just need to look at what your Job Type cbo field is based on. text or int. This will change the syntax below.

Private sub Dblclick_FindJobID
If IsNull(Me.JobID) Then Exit sub
DoCmd.OpenForm "frm_update job information", , , "[JobID]=" & Me.listbox & " and [JobTypeID]=" & me.listbox.Column(5)

End Sub

Also watch for spaces in your form names.

Let me know if this helps

Regards
BentBrain

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Aakburns
post Feb 7 2020, 02:56 PM
Post#3



Posts: 17
Joined: 4-December 19



Do I need a line for each job type of the 'DoCmd" command?

I am also just using row source with names typed in for the property, not a table with a list of job types.

What does 'me.listbox' refer to here? Or what should field should I be changing that to instead of what you put here?



I'm obviously doing something wrong here, but here's my code and the error.
CODE
Private Sub Job_ID_DblClick(Cancel As Integer)
If IsNull(Me.Job_ID) Then Exit Sub
DoCmd.OpenForm "frm_update job information", , , "[JobID]=" & Me.JobType & " and [JobType]=" & Me.JobType.Column(1)
End Sub

This post has been edited by Aakburns: Feb 7 2020, 03:04 PM
Attached File(s)
Attached File  Annotation_2020_02_07_135559.png ( 14.33K )Number of downloads: 0
Attached File  Annotation_2020_02_07_140325.png ( 7.44K )Number of downloads: 0
 
Go to the top of the page
 
BentBrain
post Feb 8 2020, 04:19 AM
Post#4



Posts: 517
Joined: 10-February 03
From: Thailand


HI Aakburns

Based on what you have said,

I made a sample form with dlb click event to show you how to select different forms based on the job type.
I have also done a linked cbo box with table as proper example for database normalization.

Take a look and see if this help you to understand

Regards
BentBrain.

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Aakburns
post Feb 11 2020, 11:29 AM
Post#5



Posts: 17
Joined: 4-December 19



Hi BentBrain, I was out of town for the weekend and Monday, was there supposed to be an attachment here? I appreciate your help in this.
Go to the top of the page
 
BentBrain
post Feb 11 2020, 07:40 PM
Post#6



Posts: 517
Joined: 10-February 03
From: Thailand


Sorry here is the attachment
This post has been edited by BentBrain: Feb 11 2020, 07:41 PM
Attached File(s)
Attached File  open_example.zip ( 38.62K )Number of downloads: 2
 

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Aakburns
post Feb 12 2020, 11:44 AM
Post#7



Posts: 17
Joined: 4-December 19



Thank you so much. I'm not using a table as you are. So I'm trying to do this a little different using the rowsource for this cbo field.

I used separate lines of code but it opens all the forms in the code rather than just based on the cbo name. How can I modify this to just open one form for each? Or do I have to do it with a table?


QUOTE
Private Sub Job_ID_DblClick(Cancel As Integer)
If IsNull(Me.Job_ID) Then Exit Sub
DoCmd.OpenForm "frm_update job information", , , "[Job_ID]=" & Me.Job_ID & " and [JobType]=" & JobType.RowSourceType = "Parking Structure"
DoCmd.OpenForm "frm_Add new job_paint", , , "[Job_ID]=" & Me.Job_ID & " and [JobType]=" & JobType.RowSourceType = "Truck Washing"
End Sub

This post has been edited by Aakburns: Feb 12 2020, 11:47 AM
Go to the top of the page
 
Aakburns
post Feb 12 2020, 12:59 PM
Post#8



Posts: 17
Joined: 4-December 19



I also want to be clear here. The sample project you sent is fantastic and absolutely works, I replicated it in a copy of my database. I thank you for this, it's a solid solution after it's setup. Still curious if row source with names was possible to get this done.
Go to the top of the page
 
BentBrain
post Feb 12 2020, 08:37 PM
Post#9



Posts: 517
Joined: 10-February 03
From: Thailand


Great to hear, Yes you can just use the case statement and alter it to use your JobType Text.

Private Sub Job_ID_DblClick(Cancel As Integer)
If IsNull(Me.Job_ID) Then Exit Sub

Select Case Me.JobTypeID

Case 1 'if case 1 Truck washing then open this form
DoCmd.OpenForm "frm_update job information-Truck washing", , , "[JobID]=" & Me.JobType = "Truck Washing"

Case 2 'if case 1 Miscellaneous then open this form
DoCmd.OpenForm "frm_update job information-Miscellaneous", , , "[JobID]=" & Me.JobType = "Miscellaneous"

Case 3 'if case 1 Parking Structure then open this form
DoCmd.OpenForm "frm_update job information-Parking Structure", , , "[JobID]=" & Me.JobType = "Parking Structure"

End Select

End Sub

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
BentBrain
post Feb 12 2020, 10:39 PM
Post#10



Posts: 517
Joined: 10-February 03
From: Thailand


Hi Aakburn
Sorry i had some mistakes on that last post. Change the code to this

If IsNull(Me.Job_ID) Then Exit Sub

Select Case Me.JobType ' CHANGE HERE to text field not ID field

Case 1 'if case 1 Truck washing then open this form
DoCmd.OpenForm "frm_update job information-Truck washing", , , "[JobID]=" & Me.Job_ID & Me.JobType = "Truck Washing"


etc.....


Enjoy BB

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Aakburns
post Yesterday, 01:53 PM
Post#11



Posts: 17
Joined: 4-December 19



Fantastic stuff, thanks for all your help, everything works great. I definitely got some better understanding about things from all of this.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th February 2020 - 01:05 AM