recordsetclone question

[ElizCat]

I have a form with two combo boxes, Style and BlendID. For each new record,
if the user selects an existing Style, I want to display the corresponding
BlendID. If a new Style is typed in, I want the user to be able to select an
existing BlendID or enter a new one. Complicating matters, when a BlendID is
selected, I want to display a pop-up form showing Blend details
(BlendID-AfterUpdate event)

I'm having a hard time gettng the Style-BlendID link to work without
scrambling the link between my BlendID combo and the popup form. I've tried
a filtered query to limit the values available to the BlendID combo, but I
had some indecipherable problems (no errors, just not working). So, I
thought maybe working with RecordsetClone would be the way to go, but I'm
stuck on how to get my second combo box to display the recordset retreived
for the first. Any suggestions?

Thanks in advance - ElizCat

Here's what I've got:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'do something here to pass [BlendID] to the BlendID combo box

Call BlendID_AfterUpdate

 

[Klatuu]

My guess is that you Dimmed your object references in one of the procedures.
This makes them visible only to that procedure. Try moving the Dim
statements for your recordset clone to the top of your form module. That
way, they are visible to all procedures in the module.

 

[ElizCat]

Oh dear, I think you might have lost me. I'm really inexperienced as a
programmer.

When you say to try moving the Dim statements for the recordset clone to the
top of the form module, do you mean to put the "Dim rs as Object" statement
in the General Declarations section?

 

[Klatuu]

Exactly.

Oh dear, I think you might have lost me. I'm really inexperienced as a
programmer.

When you say to try moving the Dim statements for the recordset clone to the
top of the form module, do you mean to put the "Dim rs as Object" statement
in the General Declarations section?

 

[ElizCat]

Okay, I can't seem to add anything to the General Declarations section. If I
put it in a public Sub, will that work?

thanks for your help.
ElizCat

 

[ElizCat]

BTW, I'm working in Access 2002 (although my dbase seems to have saved as
2000).

Putting the Dim statement in a public sub does not seem to have the intended
effect. When I select an existing Style from the combo box, the BlendID
combo box does not move to the associated BlendID. In addition, while my
BlendID_AfterUpdate event pops up the new form, it displays the first item
from the BlendID table instead of the BlendID associated with the selected
Style.

Arrrgh!

 

[Klatuu]

It doesn't go in a sub. It goes right after the Option statements at the top
of the form's module. Here is an example:

Option Compare Database
Option Explicit
Option Base 0

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object
Private xlChartObj As Object 'Chart Object for Charts
Private rstActual As Recordset 'Recordset to load Actual Data
Private rstPlan As Recordset 'Recordset to load Plan Data
Private rstItms As Recordset 'Recordset to load ITM/Program Manager
Name

 

[ElizCat]

Thank you for your patience and willingness to shepherd newbies like me.

I've put "Private rs as Object" in the General Declarations at the top of
the form module & saved it. When I go through the form and check the new
code, I still get the same behavior.

My data sources for the Style combo box and the BlendID combo box are two
different tables. Would that cause the problem?

I really don't know anything about working with recordsets, and the six
(yes, count 'em, six) reference books I have on my desk aren't being much
help. If there is an online resource you could point me toward as well, I
would be most grateful.

Best Regards (and a happy Friday!)
ElizCat

 

[Klatuu]

Post your code. It is hard to tell from here. The different recordsources
for the combos should not be causing this.

 

[ElizCat]

Okay, here's the code for the two subs I'm having problems with.
thanks again
---------------------------------------
Option Compare Database
Private rs As Object

----------------------------------------
Private Sub StyleNumber_AfterUpdate()
'if StyleNumber already exists, display BlendID on record

' Dim rs As Object 'moved this to the declarations section for testing

Set rs = Me.Recordset.Clone
rs.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Call BlendID_AfterUpdate

End Sub

----------------------------------------------------------
Private Sub BlendID_AfterUpdate()

'If the BlendID is blank, then exit the Sub.
If IsNull(Me.BlendID) Then
Exit Sub
End If

'Dimension variables.
Dim FormName As String, SyncCriteria As String
Dim F As Form, rs As Object
'Set the formname to "BlendForm," the form that will be
'synchronized.
FormName = "BlendForm"

'Check to see if BlendForm is open. If it
'is not open, open it.
If Not fIsLoaded("BlendForm") Then
DoCmd.OpenForm FormName
End If

'Define the form object and Recordset object for
'the Products form.
Set F = Forms(FormName)
Set rs = F.Recordset.Clone

'Define the criteria used for the synchronization.
SyncCriteria = "[BlendID] =" & Me![BlendID]

'Synchronize the corresponding record in BlendForm to
'the current record in the subform.
rs.FindFirst SyncCriteria

'If a record exists in Blendform, find the
'matching record.
If rs.EOF Then
MsgBox "No match exists!", 64, FormName
Else
F.Bookmark = rs.Bookmark
End If

End Sub

 

[Klatuu]

I made a few changes. Take a look, try to run it. I will not guarantee it
will work first time around, but it is closer. Notice I put your reference to
rs back in the sub. It really doesn't need to be outside. There were a few
problems, but I think I have them fixed.

Option Compare Database

----------------------------------------
Private Sub StyleNumber_AfterUpdate()
Dim rs As Recordset

'if StyleNumber already exists, display BlendID on record

Set rs = Me.RecordsetClone
With rs
'Since this is a string, it needs quotes
.FindFirst "[StyleNumber] = '" & str(Nz(Me![BlendID], 0)) & "'"
If Not .NoMatch Then
Me.Bookmark = rs.Bookmark
End If
.Close
End With
Set rs = Nothing

If Not IsNull(Me.BlendID) Then ' Do it here so you save doing a call if
not needed
Call BlendID_AfterUpdate
End If

End Sub

----------------------------------------------------------
Private Sub BlendID_AfterUpdate()
Dim FormName As String, SyncCriteria As String
Dim F As Form, rsF As Database ' Need a different name here, because rs is
used

'My Guess is you still need to check here
If IsNull(Me.BlendID) Then
Exit Sub
End If
'Set the formname to "BlendForm," the form that will be
'synchronized.
FormName = "BlendForm"

'Check to see if BlendForm is open. If it
'is not open, open it.
If Not fIsLoaded(FormName) Then
DoCmd.OpenForm FormName
End If

'Define the form object and Recordset object for
'the Products form.

Set F = Forms(FormName)
Set rsF = F.RecordsetClone

'Define the criteria used for the synchronization.
'Again, if it is string, it needs quotes
SyncCriteria = "[BlendID] ='" & Me![BlendID] & "'"

'Synchronize the corresponding record in BlendForm to
'the current record in the subform.
With rsF
.FindFirst SyncCriteria
'If a record exists in Blendform, find the
'matching record.
If .NoMatch Then
MsgBox "No match exists!", 64, FormName
' What is 64. best to use standard vb constants for readability
Else
F.Bookmark = .Bookmark
End If
.Close
End With
Set rsF = Nothing
Set F = Nothing
End Sub

Okay, here's the code for the two subs I'm having problems with.
thanks again
---------------------------------------
Option Compare Database
Private rs As Object

----------------------------------------
Private Sub StyleNumber_AfterUpdate()
'if StyleNumber already exists, display BlendID on record

' Dim rs As Object 'moved this to the declarations section for testing

Set rs = Me.Recordset.Clone
rs.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Call BlendID_AfterUpdate

End Sub

----------------------------------------------------------
Private Sub BlendID_AfterUpdate()

'If the BlendID is blank, then exit the Sub.
If IsNull(Me.BlendID) Then
Exit Sub
End If

'Dimension variables.
Dim FormName As String, SyncCriteria As String
Dim F As Form, rs As Object
'Set the formname to "BlendForm," the form that will be
'synchronized.
FormName = "BlendForm"

'Check to see if BlendForm is open. If it
'is not open, open it.
If Not fIsLoaded("BlendForm") Then
DoCmd.OpenForm FormName
End If

'Define the form object and Recordset object for
'the Products form.
Set F = Forms(FormName)
Set rs = F.Recordset.Clone

'Define the criteria used for the synchronization.
SyncCriteria = "[BlendID] =" & Me![BlendID]

'Synchronize the corresponding record in BlendForm to
'the current record in the subform.
rs.FindFirst SyncCriteria

'If a record exists in Blendform, find the
'matching record.
If rs.EOF Then
MsgBox "No match exists!", 64, FormName
Else
F.Bookmark = rs.Bookmark
End If

End Sub

 

[ElizCat]

Omigosh! Thank you so much for taking the time to root through my code!
You're a God (unless that offends you, in that case, please accept my
sentiment that you're a superhero!)

I've plugged it in, and Access is having issues with the structure used in
the With...End With part in the SampleNumber_AfterUpdate module. It doesn't
like the phrase

.FindFirst "[StyleNumber] = '" & str(Nz(Me![BlendID], 0)) & "'"

because it thinks it should have an object. I've never used With before, so
I don't see anything obvious. I'll check some of my reference books, but if
you could recheck your modifications I'd be grateful.

 

[Brian Bastl]

Hi ElizCat,

1. The logic in your "rs.FindFirst" is wrong. It will find no matches
because Style <> Blend. Style = Style, and Blend = Blend.

2. Do you have a StyleNumber as a FK in your Blends table, or a BlendID as a
FK in your Styles table? Any field which defines the relationship between
the two tables? Perhaps you could post the relevant table structures.

tblStyles(*StyleNumber, Style, etc)
tblBlends(*BlendID, Blend, +StyleNumber, etc)

3. Assuming that the above table structures vaguely resemble what's in your
db, the rowsource for BlendID (using the query builder) would look like the
following:
SELECT BlendID, Blend
FROM tblBlends
WHERE ((([StyleNumber]) = Forms!MyFormName!Style))

In the AfterUpdate event of your Style combobox (after you've selected a
style), you'd issue a command to requery your BlendID combobox:

Private Sub Style_AfterUpdate()
Me.BlendID.Requery
End Sub

Or if you want to set its rowsource programmatically, you'd use the
following in the AfterUpdate event of your Style combobox with no requery
necessary:

Private Sub Style_AfterUpdate()
Me.BlendID.Rowsource = "" & _
"SELECT BlendID, Blend " & _
"FROM tblBlends " & _
"WHERE tblBlends.StyleNumber =" & Me.Style
End Sub

5. If you want to programmatically select the first item in the combo, then
you can add the following line to the end of the code above:

Me.BlendID = Me.BlendID.ItemData(0)


HTH,
Brian

 

[Klatuu]

Brian has a good point. Is it reasonable that Style should = Blend? In any
case, the FindFirst line as is is expecting Me![BlendID to be character data.
If it is numeric data, then it should be like this:
..FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0))

The With End With structure is just a shorthand method of referencing
properties of an object. For example, you could write code like this:
Me.txtSomeControl = "Foo"
Me.txtSomeOtherControl = "Boo"
Me.txtSomeOtherControl.Locked = True

Using the With End With, it would be like this:
With Me
.txtSomeControl = "Foo"
.txtSomeOtherControl = "Boo"
.txtSomeOtherControl.Locked = True
End With

So that everything that start with a period between With and End with is
considered a proprerty of the With object. In this case, the current form.

 

[ElizCat]

Hi Brian and Klatuu!

Thanks to you both for the explanations and code. Brian is correct, Style
<> Blend, and so I will have to look at his code to help me set it up to find
the right info from the right tables. Klatuu, you had a good point - BlendID
is a number, Style is text. I will have to adjust my code to account for
that. Blend and Style are related in the following manner:

Table Sample (SampleID PK, Style, BlendID FK, other data)
Table Blend (BlendID PK, other data)

I don't have Style set up as its own table right now, although it would be
easy enough to do that if needed.

I don't know why my version of Access isn't liking the With End With
structure. Obviously I can work around that though.

Thank you both very much for helping me plow through this. I'm on vacation
right now in Savannah, so it may be a few days before I get motivated to work
through all this, but I'll let you know how it turns out.

warmest regards,
ElizCat

 

[ElizCat]

Back from vacation, having made some of your suggested modifications, and I'm
still having issues...

I'm apparently missing some punctuation in the first If statement, and I
can't figure out what I've got wrong. Do you guys see anything obvious? As
reference, BlendID is a number and is the PK in the Blend Table and a FK in
the Samples table. StyleNumber is just a text field (because of our
numbering convention) in the Samples table.

If Not IsNull(Me.StyleNumber) Then
'if StyleNumber already exists, display BlendID on record
Me.BlendID.RowSource = "" & _
"SELECT BlendID" & "FROM Samples" & _
"WHERE Samples.StyleNumber =" & "Me.StyleNumber"
End If

If Not IsNull(Me.BlendID) Then 'display blend info as usual
Call BlendID_AfterUpdate
ElseIf IsNull(Me.BlendID) Then
'set the datasource for BlendID back to the Blend Table
Me.BlendID.RowSource = Blend.BlendID
End If

 

[ElizCat]

Klatuu, my version of Access still doesn't like the following statement:

rsF.FindFirst "[StyleNumber] = " & str(Nz(Me![BlendID], 0))

I get the error "Method or data member not found" . I've gone through MS
help to try to understand the error message, and I can't figure out what the
problem is. Any ideas?

thanks again -
ElizCat

 

[Brian Bastl]

Welcome back,

If StyleNumber is a numeric datatype then the following will work:

If Not IsNull(Me.StyleNumber) Then
'if StyleNumber already exists, display BlendID on record
Me.BlendID.RowSource = "" & _
"SELECT BlendID FROM Samples " & _
"WHERE Samples.StyleNumber =" & Me.StyleNumber
End If

Otherwise, if StyleNumber is textual then you'll need additional quotes in
your WHERE clause:

"WHERE Samples.StyleNumber ='" & Me.StyleNumber & "'"

Brian

 

[ElizCat]

Brian,
thank you very much for helping me through the syntax and punctuation
pitfalls! My combo box link now works perfectly! Now, if I can just get the
BlendID_AfterUpdate event to work properly, my major hurdle will be crossed.

thanks again so very much for your patient help! I cannot begin to convey
my appreciation for your kind and selfless donation of time and experience to
help newbies such as myself.

all my best -
ElizCat

 

[Brian Bastl]

Glad to be of service. Post back if you need help with the BlendID portion.

Brian