Replace Values instead by FORM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
It'd be appreciate if the "QUERY" can do instead of inputing values by the
"FORM". The table is lack of the values, so I would like to place them,
whether it could be done by the "QUERY". The values is as the same as the
first beforehand records such as: 33, 0 ,0 ,0 34 ,0 ,0 , 35,
0,36,37,38,0,.... So the values to replace zero should be the one before and
it should then be 33,33,33,33,34,34,34,35,35,36,37,38,38,...

Thanks,
 
First BackUp your data.

Try this code

Function FillNumbers()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Dim OldNum As Integer
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("Select * From TableName Order By KeyField")

While Not MyRec.EOF
If MyRec!MyNum = 0 Then
MyRec.Edit
MyRec!MyNum = OldValue
MyRec.Update
Else
OldValue = MyRec!MyNum
End If
MyRec.MoveNext
Wend
End Function

Where:
MyNum = name of the field to be updated
KeyField = name of the field used to sort the records
 
Hello,

Thanks for your help it works. By the way, I would like to update on a
certain record. Now, no movefirst or movelast or do unitl eof. Is there
anything like this? Update on a certain 'ID' .
Thanks,
 
TO get just one record

MyDb.OpenRecordset("SELECT * FROM TableName WHERE PrimaryKeyField=
SomeSpecificValue")

To update just one record

Currentdb().Execute "UPDATE TableName SET SomeField = SomeValue WHERE
PrimaryKeyField= SomeSpecificValue"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi,


It ends with dialog bug says: Too few parameters expected 3

and here is the code :

Dim MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Set MyDB = CurrentDb()

MyDB.OpenRecordset ("SELECT * FROM tblFill")

'To update just one record

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE MyRec!ID= me.txtsubj"

Thanks, hee hee
 
If you decide to use and update SQL, there is no need to open a record set

/ Remove this **************
Dim MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Set MyDB = CurrentDb()

MyDB.OpenRecordset ("SELECT * FROM tblFill")
/ Until here ****************************


About the SQL , there are few mistakes
1. running this code it's like running an update query, so it doesn't
recognize me.txtsubj, either change it to Forms![FrmForm copy]![txtsubj]
Or, take it outside the string like:

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE MyRec!ID= " & me.txtsubj

2. MyRec!ID= me.txtsubj - drop the MyRec, it looks strait in the table,
without the RecordSet that we removed

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE ID= " & me.txtsubj

3. You are setting Paragraph1 with the same value that you are filtering on
(Where condition), which mean that the value already exist in the table but
in another field, I'm sure that is not what tou want.

4. Make sure that the text boxes in the form has value in them.

--
Good Luck
BS"D


ooxx said:
Hi,


It ends with dialog bug says: Too few parameters expected 3

and here is the code :

Dim MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Set MyDB = CurrentDb()

MyDB.OpenRecordset ("SELECT * FROM tblFill")

'To update just one record

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE MyRec!ID= me.txtsubj"

Thanks, hee hee


John Spencer said:
TO get just one record

MyDb.OpenRecordset("SELECT * FROM TableName WHERE PrimaryKeyField=
SomeSpecificValue")

To update just one record

Currentdb().Execute "UPDATE TableName SET SomeField = SomeValue WHERE
PrimaryKeyField= SomeSpecificValue"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
Assumptions:
--Paragraph1 is a text field
--ID is a number field

CurrentDb().Execute _
"UPDATE tblFill" & _
" SET Paragraph1 = """ & [Forms]![FrmForm copy]![txtsubj] & _
""" WHERE ID=" & me.txtsubj


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ooxx said:
Hi,


It ends with dialog bug says: Too few parameters expected 3

and here is the code :

Dim MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Set MyDB = CurrentDb()

MyDB.OpenRecordset ("SELECT * FROM tblFill")

'To update just one record

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE MyRec!ID= me.txtsubj"

Thanks, hee hee


John Spencer said:
TO get just one record

MyDb.OpenRecordset("SELECT * FROM TableName WHERE PrimaryKeyField=
SomeSpecificValue")

To update just one record

Currentdb().Execute "UPDATE TableName SET SomeField = SomeValue WHERE
PrimaryKeyField= SomeSpecificValue"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
Thank,

I have try both of you. My work is now solved.
Thanks a lot, oh yeah this is done. Works fine on the blank form.

John Spencer said:
Assumptions:
--Paragraph1 is a text field
--ID is a number field

CurrentDb().Execute _
"UPDATE tblFill" & _
" SET Paragraph1 = """ & [Forms]![FrmForm copy]![txtsubj] & _
""" WHERE ID=" & me.txtsubj


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ooxx said:
Hi,


It ends with dialog bug says: Too few parameters expected 3

and here is the code :

Dim MyDB As DAO.Database
Dim MyRec As DAO.Recordset
Set MyDB = CurrentDb()

MyDB.OpenRecordset ("SELECT * FROM tblFill")

'To update just one record

CurrentDb().Execute "UPDATE tblFill SET Paragraph1 = [Forms]![FrmForm
copy]![txtsubj] WHERE MyRec!ID= me.txtsubj"

Thanks, hee hee


John Spencer said:
TO get just one record

MyDb.OpenRecordset("SELECT * FROM TableName WHERE PrimaryKeyField=
SomeSpecificValue")

To update just one record

Currentdb().Execute "UPDATE TableName SET SomeField = SomeValue WHERE
PrimaryKeyField= SomeSpecificValue"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

Thanks for your help it works. By the way, I would like to update on a
certain record. Now, no movefirst or movelast or do unitl eof. Is
there
anything like this? Update on a certain 'ID' .
Thanks,


:

First BackUp your data.

Try this code

Function FillNumbers()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset
Dim OldNum As Integer
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("Select * From TableName Order By
KeyField")

While Not MyRec.EOF
If MyRec!MyNum = 0 Then
MyRec.Edit
MyRec!MyNum = OldValue
MyRec.Update
Else
OldValue = MyRec!MyNum
End If
MyRec.MoveNext
Wend
End Function

Where:
MyNum = name of the field to be updated
KeyField = name of the field used to sort the records
--
Good Luck
BS"D


:

Hi
It'd be appreciate if the "QUERY" can do instead of inputing values
by
the
"FORM". The table is lack of the values, so I would like to place
them,
whether it could be done by the "QUERY". The values is as the same
as
the
first beforehand records such as: 33, 0 ,0 ,0 34 ,0 ,0 , 35,
0,36,37,38,0,.... So the values to replace zero should be the one
before and
it should then be 33,33,33,33,34,34,34,35,35,36,37,38,38,...

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top