Opening another Form on a Record (with Combo and Lists boxes) 2

[gal4y]

I have a form that I would like to open based on a user input on another form. The problem is I need to open the other form not only on a specific record but the combo and lists boxes have to be activated based on that record.
Form A (MDEP Form) is where users input data and then Form B (Score Manager) is where users score projects.

I want users to input new project into Form A (Got that part). I have the Score Manager Set up. Right now the Score Manager opens on Record 1 (not 90021) but moves to the right Project Type (on the combo; Me.txtResourceFramework.Value) and MDEP ID (on the list; Me.txtMDEPID.Value box) but does not move the entire form to the specific record (90021 as an example).
Here is my code. Can someone help?
Thanks Greg


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Scoring: Score Manager"
stLinkCriteria = "[MDEP/Capability ID] = " & 90021
DoCmd.OpenForm stDocName, acNormal, , , , acWindowNormal, stLinkCriteria

Forms![Scoring: Score Manager]!ComboProjectType = Me.txtResourceFramework.Value
Forms![Scoring: Score Manager]!ComboProjectType.Requery
Forms![Scoring: Score Manager]!lstMDEPID = Me.txtMDEPID.Value
Forms![Scoring: Score Manager]!lstMDEPID.Requery

 

[RoyVidar]

Hi!

You're sending the link criteria as openargs, not as a where condition. Using that you'd have to issue a .findfirst in the other form to get to it (on load?). Have you tried using it as a where condition?

[tt]DoCmd.OpenForm stDocName, acNormal, ,stLinkCriteria , , acWindowNormal[/tt]

Populating combos etc, I'm more into pulling than pushing, so I'd use the other forms on load event to pull the information:

[tt]Me!ComboProjectType = Forms!OtherForm!txtResourceFramework.Value
Me!ComboProjectType.Requery
Me!lstMDEPID = Forms!OtherForm!txtMDEPID.Value
Me!lstMDEPID.Requery[/tt]

The "OtherForm" needs to be open for this, but you could hide it, if that's an issue.

Roy-Vidar

 

[gal4y]

Roy-Vidar,

Can you give the recipe for doing that. I tried the where. No luck. It acts as a filter. I need all the records.

I would be interested in trying the extra form and hiding it. Just not sure how to do that.

Thanks for any help

Greg

 

[RoyVidar]

Yup "The problem is I need to open the other form not only on a specific record..." - that's usually a filter.

So you need all the records. Ok, try this: Do as in first reply, send the criteria as openargs, then in the on load event of the form you're opening:

[tt]dim rs as dao.recordset
if not isnull(me.openargs) then
set rs=me.recordsetclone
rs.findfirst me.openargs
if not rs.nomatch then
me.bookmark=rs.bookmark
end if
set rs=nothing
end if[/tt]

- typed not tested

Needs a reference to Microsoft DAO 3.# Object Library, found in the Tools | References menu in VBE.

Roy-Vidar

 

[gal4y]

Worked like a charm.

Thank you so much

Greg

 

[LowBrow]

Hi again Roy-Vidar,
I find myself with another, similar problem that you have almost answered for me. A form in my db will allow users to open an existing report (this is actually a 'form' not a report) to edit. It operates from three combo boxes cboClientSel, AND cboComp_Date (the date the report originated) OR cboAIR_Date (the date the incident being reported actually occurred). The user selects a client name. From this selection, the db produces lists of all reports for that client and populates the other two combo boxes. The user then selects either the comp date or the AIR date. This part works fine. However, when I click the open form button, I get a type mismatch error. Here is the code I am using to open the form to the correct record. I have tried multiple combinations of data types in the dim statements (using I believe appropriate "'" or "#" as necessary), and keep getting the same error (Type Mismatch). I am very confused at this point.

Private Sub cmdOpen_Edit_Click()
On Error GoTo Err_cmdOpen_Edit_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As Date

stDocName = "frmUpDate_Exist"

If cboComp_Date = Null Then
stLinkCriteria = "[ConID] = " & "'" & Me![cboClientSelect] & "'" And _
stLinkCriteria2 = "[AIR_Date]= " & "#" & Me![cboAIR_Date] & "#"
Else: stLinkCriteria = "[ConID] = " & "'" & Me![cboClientSelect] & "'" And _
stLinkCriteria2 = "[Comp_Date]= " & "#" & Me![cboComp_Date] & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria And stLinkCriteria2

Exit_cmdOpen_Edit_Click:
Exit Sub

Err_cmdOpen_Edit_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Edit_Click

End Sub

 

[RoyVidar]

There are some issues here, one of them is to post a new thread for new questions, and not try to call upon some specific member
1 - it might be perceived as impolite
2 - the question might get less attention by other members, and they might be discouraged from answering - check out this faq on how to get the most out of the membership faq181-2886.</rant>

To use more than one criteria, they must be concatinated. Using the And operator on two strings, would create some errors, but prior to that, there are errors in the null test and the assigning of criterias.

1 Delimiters
- strings - single quotes
- dates - hash (#)
- numerics - none

2 SQL-string
- in a valid sql string, the where condition when using more than one criteria, then AND or Or operator is whithin the string

3 Null test - use the IsNull function

I'm guessing the combos values are numeric, no delimiters (else just put in the single quotes), then something like this might work:

[tt]If isnull(me!cboComp_Date) Then
stLinkCriteria = "[ConID] = " & Me![cboClientSelect] & _
" And [AIR_Date]= #" & Me![cboAIR_Date] & "#"
Else
stLinkCriteria = "[ConID] = " & Me![cboClientSelect] & _
" And [Comp_Date]= #" & Me![cboComp_Date] & "#"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria[/tt]

Roy-Vidar

 

[LowBrow]

Thanks Roy-Vidar,
I appreciate the etiquette instruction as well and will respect it in the future. Of the several years I have been using this forum I have always started new threads as you state. It was only in my last posting where you assisted me that I posted to an old thread. I was searching for help with this problem and saw that you had responded here as well.

In the future, I will make the faux-pas. Thanks for your help, again.

This code is similar to what I started with, but I had found some other postings that lead me in the direction of what I posted here. However, the IsNull is a significant difference. That with the improper use of single quotes for the conID selection is most likely the root of my troubles.