Opening different forms based on combobox value

[yetstar]

Hi all

Ok, I'll try and explain this as clearly as possible

I've got a combo box, where the user selects a reference number.

A query uses this reference number to return all details in the record relating to it.

A form uses the results returned from the query to show all of the details which the user can then update.

Standard stuff so far huh?

BUT!

What I want to happen, is, when the query returns the results - there is a 'Category' field which contains either A,B,C or D.

I have an A form, a B form, a C form and a D form.......

I want the correct form to open based on the 'Category' result from the query.

Does anybody know how I do this?

Many thanks!!
Yet

 

[wazz]

you want a DLookup to find: the category - in your query - where the Ref = yourCombo. then open the correct form based on that result.

 

[yetstar]

Thanks Wazz, I'll have a look around the net for some info on DLookups (havent used them before but I'm sure they arent too hard....)

I'll let you know how I get on

 

[yetstar]

Hmm...I dont think this will work.

Once the query results are returned, I need to then open 1 of 4 forms based on a particular column within the results. I cant see how a DLookup would do that?

What I need is some code or something that will basically say:

IF query result is "D", open form D - otherwise if query result is "C", open form "C" etc....

Sorry, I'm an excel bod, trying to get my head around Access code is like a car mechanic trying to fix a plane

Cheers!

 

[yetstar]

Here's where I am with the code (trying the DLookup function!)

It runs the query, then closes it instantly without opening any forms

Can anyone identify where the error is?



Private Sub cmdUpdateRecord_Click()
On Error GoTo Err_cmdUpdateRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

'runs the query which returns the relevant record from the combobox

stDocName = "qryOpenUpdateForm"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Identifies the Business Unit and opens the relevant form

If (DLookup("[Business Unit]", "qryOpenUpdateForm", "[Business Unit]" = "A")) Then
DoCmd.OpenForm frmAUp, , , stLinkCriteria

Else

If (DLookup("[Business Unit]", "qryOpenUpdateForm", "[Business Unit]" = "B")) Then
DoCmd.OpenForm frmBUp, , , stLinkCriteria

Else

If (DLookup("[Business Unit]", "qryOpenUpdateForm", "[Business Unit]" = "C")) Then
DoCmd.OpenForm frmCUp, , , stLinkCriteria

Else

If (DLookup("[Business Unit]", "qryOpenUpdateForm", "[Business Unit]" = "D")) Then
DoCmd.OpenForm frmDUp, , , stLinkCriteria


End If
End If
End If
End If

'Closes the query

DoCmd.Close acQuery, "qryOpenUpdateForm"

Exit_cmdUpdateRecord_Click:
Exit Sub

Err_cmdUpdateRecord_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateRecord_Click

End Sub

 

[wazz]

i'm asuming here that the reference num in your cbobox is unique. should be something along these lines:

Dim strBusUnit as String

strBusUnit = DLookup("[Business Unit]", "qryOpenUpdateForm", "[ReferenceNum] = " & Me.cboRefNum)
Select Case strBusUnit 
   Case "A"
      DoCmd.OpenForm frmAUp etc.
   Case "B"
   Case "C"
   Case "D"
End Select

-the third part of the DLookup has to find something unique - an ID of some sort, like the reference number (i hope).
- you have to change the name of Me.cboRefNum to Me.YourComboboxName.
- i think that should work. you might have to move the quotes in the third part of the DLookup to the end.
- then, check out post #2 again, just for fun 'n games. let us know how it goes. w.

 

[yetstar]

i'm asuming here that the reference num in your cbobox is unique. should be something along these lines:

Dim strBusUnit as String

strBusUnit = DLookup("[Business Unit]", "qryOpenUpdateForm", "[ReferenceNum] = " & Me.cboRefNum)
Select Case strBusUnit 
   Case "A"
      DoCmd.OpenForm frmAUp etc.
   Case "B"
   Case "C"
   Case "D"
End Select

-the third part of the DLookup has to find something unique - an ID of some sort, like the reference number (i hope).
- you have to change the name of Me.cboRefNum to Me.YourComboboxName.
- i think that should work. you might have to move the quotes in the third part of the DLookup to the end.
- then, check out post #2 again, just for fun 'n games. let us know how it goes. w.

Thats lookin good Wazz, I'll try it out and let you know mate. (And yep, the ref is unique )

Thanks a mil for your help

 

[yetstar]

I've tried swapping the query to the main table (I'm not sure the query is necessary with a DLookup) and changing the Me.CboCombo1 to the full name of the form but still, all I get is the query opening then the message

You cancelled the previous operation

Any ideas?

Private Sub cmdUpdateRecord_Click()
On Error GoTo Err_cmdUpdateRecord_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim strBusUnit As String

     stDocName = "qryOpenUpdateForm"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

strBusUnit = DLookup("[Business_Unit]", "qryOpenUpdateForm", "[P_Ref] = " & Me.cboCombo1)

Select Case strBusUnit

   Case "A"
      DoCmd.OpenForm frmAUp
   Case "B"
      DoCmd.OpenForm frmBUp
   Case "C"
      DoCmd.OpenForm frmCUp
   Case "D"
      DoCmd.OpenForm frmDUp
   
End Select

DoCmd.Close acQuery, "qryOpenUpdateForm"
    
Exit_cmdUpdateRecord_Click:
    Exit Sub

Err_cmdUpdateRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateRecord_Click
    
End Sub

 

[wazz]

ok, several things, one being my mistake. so sorry.
- your query is already using criteria to get the right data for your form. so...remove the criteria from the dlookup:

strBusUnit = DLookup("[Business_Unit]", "qryOpenUpdateForm")

- you do not need: Dim stDocName As String, or Dim stLinkCriteria As String
- you do not need: stDocName = "qryOpenUpdateForm", or DoCmd.OpenQuery stDocName, acNormal, acEdit
(you are partly right: you need the query, but you don't need to OPEN the query)
- you do not need: DoCmd.Close acQuery, "qryOpenUpdateForm"
- you should also test for unexpected null fields with the IsNull and Nz functions. the final query should (i hope) look something like:

'make sure something has been selected in the combobox
If Not IsNull(Me.cboCombo1) Then
    'get the business unit from the query. if the busunit has not been
    'filled in for this record the Nz function sets the value to ""
    strBusUnit = Nz(DLookup("[Business_Unit]", "qryOpenUpdateForm"), "")
    'the (a) busunit was found (it is not "")
    If strBusUnit <> "" Then
        Select Case strBusUnit 
           Case "A"
               DoCmd.OpenForm "frmAUp"
           Case "B"
               DoCmd.OpenForm "frmBUp"
           Case "C"
               DoCmd.OpenForm "frmCUp"
           Case "D"
               DoCmd.OpenForm "frmDUp"
           'the busunit is not "", but is not a, b, c or d
           Case Else
               MsgBox "no match"
        End Select
    Else
        'the busunit is a ""
        MsgBox "no match"
    End If
'nothing selected in the cbobox.
Else
    MsgBox "no selection"
End If

 

[yetstar]

Wazz, you are a star - I'll check it out and get back to you mate, thanks again

(I thought I was doing unnecessary query opening!)

 

[yetstar]

Ok, I'm still getting an error saying 'You canceled the previous operation'

(Access cant spell cancelled!)

There is already a 'not in list' procedure on the combo box so I'll remove that and try again.

Also, shouldnt there be a Dim BusUnitstr as String?

 

[yetstar]

HA!

It was the space in the [Business Unit] that was causing the problem there!

Nice one my man! Many thanks for your help

 

[wazz]

cool. c u soon. - w