I have this piece of code than runs 1

[vix666]

I have this piece of code than runs on a form with 3 drop down boxes which the user can select from either of them and then the other form will open at the record which is specified in one of the dropdown boxes.
When i run the code it doesnt error for the first If statement but it errors for the other 2 with this error message "An expression you entered is the wrong data type for one of the arguments"

Any help would be much appreciated

Thanks

Vicky

If Me.cbo_form.Value = "Project Input" Then
If IsNull(Me.cbo_Project_id.Value) = False Then
DoCmd.OpenForm "frm_project_input"
DoCmd.GoToRecord acDataForm, "frm_project_input", acGoTo, Forms!frm_input_param.cbo_Project_id
Exit Sub
End If

If IsNull(Me.cbo_epes.Value) = False Then
DoCmd.OpenForm "frm_project_input"
DoCmd.GoToRecord acDataForm, "frm_project_input", acGoTo, Forms!frm_input_param.cbo_epes
End If

If IsNull(Me.cbo_sap.Value) = False Then
DoCmd.OpenForm "frm_project_input"
DoCmd.GoToRecord acDataForm, "frm_project_input", acGoTo, Forms!frm_input_param.cbo_sap
End If

 

[MakeItSo]

That's probably because you are referring to the Record ID in your first statement, but to Control Values in the second statement.
There is a way to kind of "lookup" the record you want to go to. I don't know it by heart but will get it to you in a moment.

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[hymn]

Question
Whats the EXIT SUB Doing Where it is

Hope this helps
Hymn

 

[vix666]

Andreas,

Thanks for looking into it for me

Hymn - the exit sub needs to be there so that if it finds a value in the cbo_project_id then it will not look at the other combo boxes as the user only selects in one of the dropdown boxes.

Thanks for your help

Vicky

 

[MakeItSo]

Hi Vicky - Got it!

Remove the two erroneous "GotoRecord",
insert this in your code:

Dim rs As Recordset
...
If IsNull(Me.cbo_Project_id.Value) = False Then
DoCmd.OpenForm "frm_project_input"
DoCmd.GoToRecord acDataForm, "frm_project_input", acGoTo, Forms!frm_input_param.cbo_Project_id
Exit Sub
End If

Set rs = CurrentDB.OpenRecordset(SQL of the query of frm_project_input)
rs.lockedits=false
rs.FindFirst "Your epes field = '" & Me.cbo_epes.Value & "'"

If Not rs.NoMatch Then Forms![frm_project_input].Bookmark = rs.Bookmark
rs.close
End If

Repeat this block (from "Set rs" on) with correct values for your second cbo-Value.

Hope this works (Cannot test at the moment)
;-)
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

This is now my code - is this right?
When i run it it errors on the last end if, saying "end if without block if"

Any ideas?

Thanks


If IsNull(Me.cbo_Project_id.Value) = False Then
DoCmd.OpenForm "frm_project_input"
DoCmd.GoToRecord acDataForm, "frm_project_input", acGoTo, Forms!frm_input_param.cbo_Project_id
Exit Sub
End If

Set rs = CurrentDb.OpenRecordset("qry_rst_project_input", dbOpenDynaset)

rs.LockEdits = False
rs.FindFirst "EP/ES = '" & Me.cbo_epes.Value & "'"

If Not rs.NoMatch Then Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If

Set rs = CurrentDb.OpenRecordset("qry_rst_project_input", dbOpenDynaset)

rs.LockEdits = False
rs.FindFirst "SAP = '" & Me.cbo_epes.Value & "'"

If Not rs.NoMatch Then Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If

 

[MakeItSo]

Yepp mea culpa!

change the lines that are like this
If Not rs.NoMatch Then Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If
to
If Not rs.NoMatch Then
Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If

and

If Not rs.NoMatch Then
Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If


MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

Now that works and i get another error - sorry for all this hassle!
It says microsoft Jet SQL can't find the form "frm_project_input"

Any ideas?

Thanks for all the help so far

Vicky

 

[vix666]

Okie ive solved that one now by myself, you need to open the form before you bookmark it.
But again i get another different error saying "Not a valid bookmark"? I have never used bookmarks before so dont really know what to do.

Any ideas?

Thanks for all the help

Vicky

 

[MakeItSo]

D'ooh, me dummy!
I left away the DoCmd.OpenForm in my code, which was originally in yours:

If Not rs.NoMatch Then
DoCmd.OpenForm "frm_project_input"
Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If

and

If Not rs.NoMatch Then
DoCmd.OpenForm "frm_project_input"
Forms![frm_project_input].Bookmark = rs.Bookmark
rs.Close
End If

That should do it now.
If not - don't hesitate to bother me:
I've given you the hint, I'll make it work!
Andy


Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

Andreas

I get another different error saying that
Forms![frm_project_input].Bookmark = rs.Bookmark is "Not a valid bookmark"? I have never used bookmarks before so dont really know what to do.

Any ideas?

Thanks for all the help

Vicky

 

[MakeItSo]

Hmpff... And I did it a different way - from the form itself...

OK:
Try this
replace the
Set rs = CurrentDb.OpenRecordset("qry_rst_project_input", dbOpenDynaset)
by
Set rs = Forms![frm_project_input].RecordsetClone

and make sure this line is positioned after the "DoCmd.OpenForm" method.

Hope this does it. Anyone else reading this thread and who's more familiar with bookmarks?

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

Well done its all solved now, i rearranged my code and put your new line in and it works great.

A star for you for being so helpful

Thanks alot

Vicky

 

[MakeItSo]

Was a pleasure, I learned som things myself...

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos