Run-Time Error 3251 Operation is not supprted for this type of o

[Guest]

I am using the code from Marshall Barton's post above...
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Defaults")
rs.FindFirst "rs.FieldName = ""P1Bill1Name"""
Me.P1Bill1Name.DefaultValue = """ & rs!FieldDefault & """""

I get this error at the rs.FindFirst line
Run-Time Error 3251 Operation is not supprted for this type of object

Could you please help me with this?
Thanks,

 

[Marshall Barton]

I am using the code from Marshall Barton's post above...
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Defaults")
rs.FindFirst "rs.FieldName = ""P1Bill1Name"""
Me.P1Bill1Name.DefaultValue = """ & rs!FieldDefault & """""

I get this error at the rs.FindFirst line
Run-Time Error 3251 Operation is not supprted for this type of object

Your syntax is messed up. try it this way:

rs.FindFirst "FieldName = """ & P1Bill1Name & """"
'that's 1, 3 and 4 quotes

Me.P1Bill1Name.DefaultValue = """" & rs!FieldDefault & """"
'that's 4 and 4 quotes

If you have trouble (and not a few folks do) with all those
quotes, you may prefer the equivalent:

rs.FindFirst "FieldName = " & Chr(34) & P1Bill1Name &
Chr(34)

Me.P1Bill1Name.DefaultValue = Chr(34) & rs!FieldDefault &
Chr(34)

Each of those are on one line regardless of any newsreader
line wrapping.

 

[Guest]

Your syntax is messed up. try it this way:

rs.FindFirst "FieldName = """ & P1Bill1Name & """"
'that's 1, 3 and 4 quotes

Me.P1Bill1Name.DefaultValue = """" & rs!FieldDefault & """"
'that's 4 and 4 quotes

If you have trouble (and not a few folks do) with all those
quotes, you may prefer the equivalent:

rs.FindFirst "FieldName = " & Chr(34) & P1Bill1Name &
Chr(34)

Me.P1Bill1Name.DefaultValue = Chr(34) & rs!FieldDefault &
Chr(34)

Each of those are on one line regardless of any newsreader
line wrapping.

Thanks for the response Marsh.
I'm still getting the same error though either way you suggested.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Defaults")
rst.FindFirst "FieldName = """ & P1Bill1Name & """"
P1Bill1Name.DefaultValue = """" & rst!FieldDefault & """"

If I use Set rst = db.OpenRecordset("Defaults", dbOpenDynaset)
I don't get the error but the code doesn't read my "Defaults" table... it
reads the form data and doesn't get past the first record in the "Defaults"
table, even if I change the code to... rst.FindFirst "FieldName = """ &
P1Bill3Name & """"
which should find the 3rd record in Defaults table.

Any help will be greatly appreciated. Thank you.

 

[Marshall Barton]

I'm still getting the same error though either way you suggested.
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Defaults")
rst.FindFirst "FieldName = """ & P1Bill1Name & """"
P1Bill1Name.DefaultValue = """" & rst!FieldDefault & """"

If I use Set rst = db.OpenRecordset("Defaults", dbOpenDynaset)
I don't get the error but the code doesn't read my "Defaults" table... it
reads the form data and doesn't get past the first record in the "Defaults"
table, even if I change the code to... rst.FindFirst "FieldName = """ &
P1Bill3Name & """"
which should find the 3rd record in Defaults table.

You need to use dbOpenDynaset for the FindFirst to be
allowed.

I thought P1Bill1Name was the name of a text box on the form
that contained the name to look up in the table. Are you
saying that "P1Bill1Name" is the name in the record you want
to find? If the latter is the case, then use:

rst.FindFirst "FieldName = ""P1Bill1Name"""
'that's 1, 2 and 3 quotes

Where is this code? In the form's Open event??

Is the name of the text box always the same as the name you
want to look up in the table?

Are you doing this for one text box or a bunch of text
boxes?

 

[Richard]

You need to use dbOpenDynaset for the FindFirst to be
allowed.

I thought P1Bill1Name was the name of a text box on the form
that contained the name to look up in the table. Are you
saying that "P1Bill1Name" is the name in the record you want
to find? If the latter is the case, then use:

rst.FindFirst "FieldName = ""P1Bill1Name"""
'that's 1, 2 and 3 quotes

Where is this code? In the form's Open event??

Is the name of the text box always the same as the name you
want to look up in the table?

Are you doing this for one text box or a bunch of text
boxes?

Marsh it looks like this line did the trick ... rst.FindFirst "FieldName =
""P1Bill1Name"""
Thank you very much.
To answer your questions.....

Where is this code? In the form's Open event??
No it's in the Form Load event.

Is the name of the text box always the same as the name you
want to look up in the table?
Yes it is. But the text in the text box can change

Are you doing this for one text box or a bunch of text
boxes?

I have 10 P1Bill1Name; P1Bill1Amt text boxes..so 20 altogether.P1Bill1,
P1Bill2, P1Bill3, etc.
I want the contents of each text box to be the default value of that text
box for the next month that is created.

Thanks again Marsh,
Richard.

 

[Marshall Barton]

Marsh it looks like this line did the trick ... rst.FindFirst "FieldName =
""P1Bill1Name"""
Thank you very much.
To answer your questions.....



I have 10 P1Bill1Name; P1Bill1Amt text boxes..so 20 altogether.P1Bill1,
P1Bill2, P1Bill3, etc.
I want the contents of each text box to be the default value of that text
box for the next month that is created.

THe reason I was asking those questions is that I think
there might be a way to make you code al shorter than doing
a FindFirst for each text box name, what some 20 times?

If the Defaults table is only used for this purpose, then
you could do all that in a short loop:

Set rst = db.OpenRecordset("Defaults")
Do Until rst.EOF
Me(rst!FieldName).DefaultValue = _
"""" & rst!FieldDefault & """"
rst.MoveNext
Loop
rst.Close: Set rst = Nothing

If the Defaults table is used fo other forms, then it might
be possible to add a little more to the table/code/??? and
still use this loop approach.

 

[Richard]

THe reason I was asking those questions is that I think
there might be a way to make you code al shorter than doing
a FindFirst for each text box name, what some 20 times?

If the Defaults table is only used for this purpose, then
you could do all that in a short loop:

Set rst = db.OpenRecordset("Defaults")
Do Until rst.EOF
Me(rst!FieldName).DefaultValue = _
"""" & rst!FieldDefault & """"
rst.MoveNext
Loop
rst.Close: Set rst = Nothing

If the Defaults table is used fo other forms, then it might
be possible to add a little more to the table/code/??? and
still use this loop approach.

Thanks again Marsh,

The "Defaults" table is only used for setting these 20 DefaultValues (for
now)
I thought there might be some way of looping. I just don't have enough
experience to know how it could be done. Now I do!

So, next question. I have used your line in my Form_Close for writing the
values from the form to "Defaults" table ...(20 times/20 records)
Dim db As Database
Set db = CurrentDb()

db.Execute "UPDATE Defaults " & "Set FieldDefault = """ & Me.P1Bill1Name &
""" " & "WHERE FieldName = ""P1Bill1Name""", dbFailOnError

So the question is, is it possible to make a loop with the db.Execute
inside??
This is just a small database I made at home that I use to plan out the
monthly bill payments.
I sure do appreciate your help Marsh!

Richard.

 

[Marshall Barton]

The "Defaults" table is only used for setting these 20 DefaultValues (for
now)
I thought there might be some way of looping. I just don't have enough
experience to know how it could be done. Now I do!

So, next question. I have used your line in my Form_Close for writing the
values from the form to "Defaults" table ...(20 times/20 records)
Dim db As Database
Set db = CurrentDb()

db.Execute "UPDATE Defaults " & "Set FieldDefault = """ & Me.P1Bill1Name &
""" " & "WHERE FieldName = ""P1Bill1Name""", dbFailOnError

So the question is, is it possible to make a loop with the db.Execute
inside??

Well you could do it by executing 20 Update queries, but it
would be easier and faster to just update the recordset:

Set rst = db.OpenRecordset("Defaults")
With rst
Do Until .EOF
.Edit
!FieldDefault = Me(!FieldName)
.Update
rst.MoveNext
Loop
End With

 

[Marshall Barton]

Marsh I ended up putting the 2 loops into one Sub called SetDefaults.
I run it right before I create a new record in my Master table.
Sub SetDefaults
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Defaults")
With rst
Do Until .EOF
.Edit
!FieldDefault = Me(!FieldName)
.Update
rst.MoveNext
Loop
End With
rst.MoveFirst
Do Until rst.EOF
Me(rst!FieldName).DefaultValue = """" & rst!FieldDefault & """"
rst.MoveNext
Loop
rst.Close: Set rst = Nothing: Set db = Nothing
End Sub
Again I really appreciate your help with this. It is ALOT cleaner
and neater than 40 lines of repititious code.
Thanks again,
Richard.

I'm not sure putting both loops in one procedure isn't doing
them more often than necessary, but it's good to see you've
got it working the way you wanted.

 

[Richard]

Well you could do it by executing 20 Update queries, but it
would be easier and faster to just update the recordset:

Set rst = db.OpenRecordset("Defaults")
With rst
Do Until .EOF
.Edit
!FieldDefault = Me(!FieldName)
.Update
rst.MoveNext
Loop
End With

--
Marsh I ended up putting the 2 loops into one Sub called SetDefaults.
I run it right before I create a new record in my Master table.
Sub SetDefaults
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Defaults")
With rst
Do Until .EOF
.Edit
!FieldDefault = Me(!FieldName)
.Update
rst.MoveNext
Loop
End With
rst.MoveFirst
Do Until rst.EOF
Me(rst!FieldName).DefaultValue = """" & rst!FieldDefault & """"
rst.MoveNext
Loop
rst.Close: Set rst = Nothing: Set db = Nothing
End Sub
Again I really appreciate your help with this. It is ALOT cleaner
and neater than 40 lines of repititious code.
Thanks again,
Richard.