Which methode can I use?

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!



I´m having a small problem with an unbound QBF form.

I´d like to populate it with the last selected value´s in each
textbox/combobox so the user don´t have to fill in all data everytime he/she
open up the form.



I have already solved how to store the values in "tblStorage" But not how to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I can use
DLookUp to get tge values but I also know that DLookUo isn´t very fast so
I´d like to know if ther´s another way?



I have tried with "Seek" but I don´t know how to fill the control with the
value with this methode.



I have trued with the code below but since the form doesn´t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
 
Use a SQL statement to OpenRecordset into tblStorage and retrieve the
values. Use the WHERE clause of the SQL string to specify the record(s) to
retrieve, if you store multiple sets of entries.

This example assumes tblStorage has these fields:
- CtlName Text The name of the control you saved a value for;
- CtlValue Text The value that you saved for this control.

Dim rs As DAO.Recordset
Dim ctl As Control
strSQL = "SELECT CtlName, CtlValue FROM tblStorage;"
Set rs = dbengine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
Me(rs!CtlName) = rs!CtlValue
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
 
Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry
 
OK!

I havn´t got time right now to test this out so I´m not sure that I´ll get
it to work. But thanks for all help!

I tblStorage I have a field which is primary key and for the example I
precifyed in my post the value in this primary key is
"CriteriaMemberShipType".

each record also have a value stored in [tblStorage].[Value]. In this field
I store last value in cboMemberShipType which is a control on my QBF-form.

Each control has it´s own primary key with it´s own value. And I only need
to get just one value for each control in the form.

I´ll take a closer look at your answer later today. I hope I´ll get it to
work otherweise I´ll post a new msg.

Thanks!
// Niklas

Allen Browne said:
Use a SQL statement to OpenRecordset into tblStorage and retrieve the
values. Use the WHERE clause of the SQL string to specify the record(s) to
retrieve, if you store multiple sets of entries.

This example assumes tblStorage has these fields:
- CtlName Text The name of the control you saved a value for;
- CtlValue Text The value that you saved for this control.

Dim rs As DAO.Recordset
Dim ctl As Control
strSQL = "SELECT CtlName, CtlValue FROM tblStorage;"
Set rs = dbengine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
Me(rs!CtlName) = rs!CtlValue
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Niklas Östergren said:
I4m having a small problem with an unbound QBF form.

I4d like to populate it with the last selected value4s in each
textbox/combobox so the user don4t have to fill in all data everytime he/she
open up the form.

I have already solved how to store the values in "tblStorage" But not
Click to expand...
how
to
populate the form again with the values stored in "tblStorage".

Offcourse is every control in this form unbound and I know that I can use
DLookUp to get tge values but I also know that DLookUo isn4t very fast so
I4d like to know if ther4s another way?

I have tried with "Seek" but I don4t know how to fill the control with the
value with this methode.

I have trued with the code below but since the form doesn4t have any
recordsource I get an error.

TIA!
// Niklas

Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"

' If a match, get the membership type ID and set value in
cboMemberShipType
' record. If no match, do nothing.
If Not rst.NoMatch Then
If Not IsNull(rst![Value]) Then
' Create clone of the form's record set.
Set rstFrm = Me.RecordsetClone

' Find the matching record.
rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]

Me.cboMemberShipType = rst![Value]

rstFrm.Close ' Close the recordset rstFrm.
End If
End If

====================================================
Click to expand...
Click to expand...
 
That seems easy and straight forward!

Thanks!
// Niklas


Perry said:
Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

Niklas Östergren said:
Hi!



I´m having a small problem with an unbound QBF form.

I´d like to populate it with the last selected value´s in each
textbox/combobox so the user don´t have to fill in all data everytime he/she
open up the form.



I have already solved how to store the values in "tblStorage" But not
Click to expand...
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I can use
DLookUp to get tge values but I also know that DLookUo isn´t very fast so
I´d like to know if ther´s another way?



I have tried with "Seek" but I don´t know how to fill the control with the
value with this methode.



I have trued with the code below but since the form doesn´t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
 
Good thought, but these values will only persist while the form is open,
i.e. this is a great approach for copying data while adding new records in
the current session. To save the values for the next time the form is open,
you will need something else. Theoretically you could open the form in
design mode, assign the default values then save and close it again. But
I'm sure this will have to be done after you have closed the running version
of the form and hence will have lost easy access to the form data.

Regards,
Kevin Rollo
Perth, Western Australia



Perry said:
Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

Niklas Östergren said:
Hi!



I´m having a small problem with an unbound QBF form.

I´d like to populate it with the last selected value´s in each
textbox/combobox so the user don´t have to fill in all data everytime he/she
open up the form.



I have already solved how to store the values in "tblStorage" But not
Click to expand...
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I can use
DLookUp to get tge values but I also know that DLookUo isn´t very fast so
I´d like to know if ther´s another way?



I have tried with "Seek" but I don´t know how to fill the control with the
value with this methode.



I have trued with the code below but since the form doesn´t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
 
Yep, you can't assign defaultvalues during runtime.

Assigning defaultvalues can be performed when the form is loading.
Either when called from another form or in it's on Form_Load event.

The only thing you need to tackle: the values ...
That's a good Eastern practise, not?
:-)

Krgrds,
Perry

Kevin Rollo said:
Good thought, but these values will only persist while the form is open,
i.e. this is a great approach for copying data while adding new records in
the current session. To save the values for the next time the form is open,
you will need something else. Theoretically you could open the form in
design mode, assign the default values then save and close it again. But
I'm sure this will have to be done after you have closed the running version
of the form and hence will have lost easy access to the form data.

Regards,
Kevin Rollo
Perth, Western Australia



Perry said:
Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

Niklas Östergren said:
Hi!



I´m having a small problem with an unbound QBF form.

I´d like to populate it with the last selected value´s in each
textbox/combobox so the user don´t have to fill in all data everytime he/she
open up the form.



I have already solved how to store the values in "tblStorage" But not
Click to expand...
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I can use
DLookUp to get tge values but I also know that DLookUo isn´t very fast so
I´d like to know if ther´s another way?



I have tried with "Seek" but I don´t know how to fill the control with the
value with this methode.



I have trued with the code below but since the form doesn´t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
Click to expand...
 
I'm afraid that Kevin is correct. Actually, unless you open the form in
Design View, the only time you can set DefaultValues is during runtime.
Since the poster is interested in the last value when the form opens, I'd
suggest that he uses Allen Browne's recordset method, except to limit the
return to the single row that is either the last record chosen by a
timestamp field, or the highest value of an incremental autonumber.
Something like (aircode):

Sub frmWhatever_Current()
Dim rst As DAO.Recordset
Dim ctl As Control
strSQL = "SELECT TOP 1 ID, * FROM tblStorage;"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
Me.Ctrl1.DefaultValue = !Field1
Me.Ctrl2.DefaultValue = !Field2
Me.Ctrl3.DefaultValue = !Field3
' ... etc.
End With
rst.Close
Set rst = Nothing
End Sub

With the code above, you really don't need to set the DefaultValue property.
Just setting the control's value accomplishes the same thing.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Perry said:
Yep, you can't assign defaultvalues during runtime.

Assigning defaultvalues can be performed when the form is loading.
Either when called from another form or in it's on Form_Load event.

The only thing you need to tackle: the values ...
That's a good Eastern practise, not?
:-)

Krgrds,
Perry

Kevin Rollo said:
Good thought, but these values will only persist while the form is open,
i.e. this is a great approach for copying data while adding new records in
the current session. To save the values for the next time the form is open,
you will need something else. Theoretically you could open the form in
design mode, assign the default values then save and close it again. But
I'm sure this will have to be done after you have closed the running version
of the form and hence will have lost easy access to the form data.

Regards,
Kevin Rollo
Perth, Western Australia



Perry said:
Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

"Niklas Östergren" <[email protected]> schreef in bericht
Hi!



I´m having a small problem with an unbound QBF form.

I´d like to populate it with the last selected value´s in each
textbox/combobox so the user don´t have to fill in all data everytime
he/she
open up the form.



I have already solved how to store the values in "tblStorage" But
Click to expand...
not
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I
Click to expand...
can
use
DLookUp to get tge values but I also know that DLookUo isn´t very
Click to expand...
fast
so
I´d like to know if ther´s another way?



I have tried with "Seek" but I don´t know how to fill the control
Click to expand...
with
the
value with this methode.



I have trued with the code below but since the form doesn´t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
Click to expand...
 
Well, Niklas, you now have lots of good ideas to choose from. :-)

The idea of using one record for each control, and storing the control name
and value has a couple of advantages. It works independently of the actual
controls on the form, e.g. if you add more controls, they will save next
time. Further, the same table can be used for saving the defaults for
different forms (assuming a field for the form name), and users (either
another field for the user, put this table in the front end).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Arvin Meyer said:
I'm afraid that Kevin is correct. Actually, unless you open the form in
Design View, the only time you can set DefaultValues is during runtime.
Since the poster is interested in the last value when the form opens, I'd
suggest that he uses Allen Browne's recordset method, except to limit the
return to the single row that is either the last record chosen by a
timestamp field, or the highest value of an incremental autonumber.
Something like (aircode):

Sub frmWhatever_Current()
Dim rst As DAO.Recordset
Dim ctl As Control
strSQL = "SELECT TOP 1 ID, * FROM tblStorage;"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
Me.Ctrl1.DefaultValue = !Field1
Me.Ctrl2.DefaultValue = !Field2
Me.Ctrl3.DefaultValue = !Field3
' ... etc.
End With
rst.Close
Set rst = Nothing
End Sub

With the code above, you really don't need to set the DefaultValue property.
Just setting the control's value accomplishes the same thing.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Perry said:
Yep, you can't assign defaultvalues during runtime.

Assigning defaultvalues can be performed when the form is loading.
Either when called from another form or in it's on Form_Load event.

The only thing you need to tackle: the values ...
That's a good Eastern practise, not?
:-)

Krgrds,
Perry
Click to expand...
records
in
the current session. To save the values for the next time the form is open,
you will need something else. Theoretically you could open the form in
design mode, assign the default values then save and close it again. But
I'm sure this will have to be done after you have closed the running version
of the form and hence will have lost easy access to the form data.

Regards,
Kevin Rollo
Perth, Western Australia



Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

"Niklas Vstergren" <[email protected]> schreef in bericht
Hi!



I4m having a small problem with an unbound QBF form.

I4d like to populate it with the last selected value4s in each
textbox/combobox so the user don4t have to fill in all data everytime
he/she
open up the form.



I have already solved how to store the values in "tblStorage" But not
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that I can
use
DLookUp to get tge values but I also know that DLookUo isn4t very fast
so
I4d like to know if ther4s another way?



I have tried with "Seek" but I don4t know how to fill the control with
the
value with this methode.



I have trued with the code below but since the form doesn4t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " & rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
Click to expand...
 
Thank you all for beeing so helpfull!

Now I have several good idéas to choose from and I think I´ll try them all
;-). But not at the same time though!

// Niklas


Allen Browne said:
Well, Niklas, you now have lots of good ideas to choose from. :-)

The idea of using one record for each control, and storing the control name
and value has a couple of advantages. It works independently of the actual
controls on the form, e.g. if you add more controls, they will save next
time. Further, the same table can be used for saving the defaults for
different forms (assuming a field for the form name), and users (either
another field for the user, put this table in the front end).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Arvin Meyer said:
I'm afraid that Kevin is correct. Actually, unless you open the form in
Design View, the only time you can set DefaultValues is during runtime.
Since the poster is interested in the last value when the form opens, I'd
suggest that he uses Allen Browne's recordset method, except to limit the
return to the single row that is either the last record chosen by a
timestamp field, or the highest value of an incremental autonumber.
Something like (aircode):

Sub frmWhatever_Current()
Dim rst As DAO.Recordset
Dim ctl As Control
strSQL = "SELECT TOP 1 ID, * FROM tblStorage;"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
Me.Ctrl1.DefaultValue = !Field1
Me.Ctrl2.DefaultValue = !Field2
Me.Ctrl3.DefaultValue = !Field3
' ... etc.
End With
rst.Close
Set rst = Nothing
End Sub

With the code above, you really don't need to set the DefaultValue property.
Just setting the control's value accomplishes the same thing.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Perry said:
Yep, you can't assign defaultvalues during runtime.

Assigning defaultvalues can be performed when the form is loading.
Either when called from another form or in it's on Form_Load event.

The only thing you need to tackle: the values ...
That's a good Eastern practise, not?
:-)

Krgrds,
Perry

"Kevin Rollo" <[email protected]> schreef in bericht
Good thought, but these values will only persist while the form is open,
i.e. this is a great approach for copying data while adding new
Click to expand...
records
in
the current session. To save the values for the next time the form is
open,
you will need something else. Theoretically you could open the form in
design mode, assign the default values then save and close it again. But
I'm sure this will have to be done after you have closed the running
version
of the form and hence will have lost easy access to the form data.

Regards,
Kevin Rollo
Perth, Western Australia



Here's another approach:

After validation of the form, change the defaultvalues of the controls
to the values before the form gets updated.
Next time the form opens, the defaultvalues will be presented, ie the
last used/saved values...

Krgrds,
Perry

"Niklas Vstergren" <[email protected]> schreef in bericht
Hi!



I4m having a small problem with an unbound QBF form.

I4d like to populate it with the last selected value4s in each
textbox/combobox so the user don4t have to fill in all data everytime
he/she
open up the form.



I have already solved how to store the values in "tblStorage"
Click to expand...
But
not
how
to
populate the form again with the values stored in "tblStorage".



Offcourse is every control in this form unbound and I know that
Click to expand...
I
can
use
DLookUp to get tge values but I also know that DLookUo isn4t
Click to expand...
very
fast
so
I4d like to know if ther4s another way?



I have tried with "Seek" but I don4t know how to fill the
Click to expand...
control
with
the
value with this methode.



I have trued with the code below but since the form doesn4t have any
recordsource I get an error.



TIA!

// Niklas



Code I have tried:

rst.Seek "=", "CriteriaMemberShipType"



' If a match, get the membership type ID and set value in
cboMemberShipType

' record. If no match, do nothing.

If Not rst.NoMatch Then

If Not IsNull(rst![Value]) Then



' Create clone of the form's record set.

Set rstFrm = Me.RecordsetClone

' Find the matching record.

rstFrm.FindFirst "[CriteriaMemberShipType] = " &
rst![Value]



Me.cboMemberShipType = rst![Value]



rstFrm.Close ' Close the recordset rstFrm.

End If

End If

====================================================
Click to expand...
Click to expand...
Click to expand...
 
Back
Top