By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

DAO edit clears the fields put in in previous form

P: n/a
I am creating a reference table of all the information used to draw a
sample. I start by first clearing the table in form1 using a delete
query.
Then after the user selects certain options, i create a string
"optionString" and put it into my table refTab in the appropriate
field.

DoCmd.OpenQuery "ClearRefTable"

Dim MyDB As Database
Dim MyRecs As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("refTable", DB_OPEN_DYNASET)

MyRecs.AddNew
MyRecs!Field1= optionString
MyRecs.Update

This works as it is supposed to.
Then in a subsequent form after I have gathered more information, I
edit the table using DAO.

Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("refTab", DB_OPEN_DYNASET)
MyRecs.MoveFirst

MyRecs.Edit

MyRecs!Field2 = name2
MyRecs!Field 3= name3

MyRecs.Update

This writes this information to the table but deletes what was written
in Field1 in the previous form. I have tried to make optionString a
Public variable (by initialising it as that in a separate module), and
writing it to the table with all the other information in the second
form instead, but this does not work. MyRecs.Update I thought saved
the table in its updated form, so do I need to somehow clear the DAO
memory in order to update specific fields on later forms? What does
making a sub public do? I have tried it and it doesn't work for this,
but maybe I have missed something?

Nothing is set in stone, so any suggestions on how to get a string
created in one form into a table with variables created in other forms
would be most appreciated.
Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
MissiMaths wrote:
I am creating a reference table of all the information used to draw a
sample. I start by first clearing the table in form1 using a delete
query.
Then after the user selects certain options, i create a string
"optionString" and put it into my table refTab in the appropriate
field.

DoCmd.OpenQuery "ClearRefTable"

Dim MyDB As Database
Dim MyRecs As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("refTable", DB_OPEN_DYNASET)

MyRecs.AddNew
MyRecs!Field1= optionString
MyRecs.Update

This works as it is supposed to.
Then in a subsequent form after I have gathered more information, I
edit the table using DAO.

Set MyDB = CurrentDb()
Set MyRecs = MyDB.OpenRecordset("refTab", DB_OPEN_DYNASET)
MyRecs.MoveFirst

MyRecs.Edit

MyRecs!Field2 = name2
MyRecs!Field 3= name3

MyRecs.Update

This writes this information to the table but deletes what was written
in Field1 in the previous form. I have tried to make optionString a
Public variable (by initialising it as that in a separate module), and
writing it to the table with all the other information in the second
form instead, but this does not work. MyRecs.Update I thought saved
the table in its updated form, so do I need to somehow clear the DAO
memory in order to update specific fields on later forms? What does
making a sub public do? I have tried it and it doesn't work for this,
but maybe I have missed something?

Nothing is set in stone, so any suggestions on how to get a string
created in one form into a table with variables created in other forms
would be most appreciated.
Thanks


I would add another line.

MyRecs.Edit
msgbox "Field1 has a value of " & MyRecs!Field1
MyRecs!Field2 = name2
MyRecs!Field 3= name3
MyRecs.Update

Chances are very high the value of Field1 is blank or null. Where else
does Field1 get updated or cleared in the program? Is there a form that
is bound to Field1 that you initialize to blank/null and it gets saved?

Open up your code window and do a database searth for !Field1 and verify
the value isn't getting reset someplace you don't expect.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.