Still looking for help writing to a table (1 Viewer)

[bodhran1]

Hi all,

I have a problem that I've been struggling with for a week.

I have a form with one text box (called "Originator") and an "OK" button.

I'd like the user to type in thier name, then when the "OK" button is clicked, I would like to write the name to a field in my table.

-------------------------------------
Private Sub Command2_Click()


user = [Forms]![Form1]![Originator]


DoCmd.OpenTable "ECRlog", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "ECRlog", acNewRec
DoCmd.GoToControl "Originator"

'what goes here?

End Sub
-------------------------------------

So far, I have the users name saved as the variable "User". I have the table ("ECRlog") open. A new record has been created and my cursor is waiting in the field called "Originator".

This part was a peice of cake.

Now I want to write the variable "User" in that field and haven't found anything in the help file or the manuals here at work.

The "real" program is a lot more complex. I've stripped it down to one form and one table with the above code to find the command. I can sent it to you if you'd like?

I've had no formal training in this so please go easy on me.



Thanks in advance!!

Dave

 

[Rich]

Why not just bind the form field to the table and "Save" the record?

 

[Rakier]

You can use code to do that in this way:

Dim MyRst As DAO.Recordset

Set MyRst = CurrentDb.OpenRecordset("ECRLog", dbOpenDynaset)

With MyRst
.AddNew
!YOURTABLESFIELDNAME = Me.Originator
.Update
End With

MyRst.Close

Set MyRst = Nothing

That should work. Of course, you will have to replace the YOURTABLEFIELDNAME with the name of the field in the table. That will then fill the field in your table with the value of the Originator field on your form.

HTH

 

[BukHix]

Oops Rakier beat me to the submit button.

This should do the trick for ya:

Private Sub cmdAddRecord_Click()

Dim strString As String
Dim rsCOD As DAO.Recordset

'Set the value of the variable to match the textbox
strString = Me.txtTextBox
    
'Open up a record set from the datbase named DatabaseName
'and then add the value of strString to the Field named
'FieldName

Set rsCOD = CurrentDb.OpenRecordset("DatabaseName")
    rsCOD.AddNew
    rsCOD!FieldName = strString
    rsCOD.Update
Set rsCOD = Nothing

End Sub

 

[bodhran1]

Not knowing much about Access, I think I tried this?

I went to the "Control Source" and used the Expression Builder to set the Value of "Originator" on my table.

When I open the form, it doesn't let me enter my name anymore.

Did I do something wrong? Can you walk me thru this?

 

[bodhran1]

I tried both your suggestions:

I get the same error message ("User-defined type not defined")running them.

For Rakier's code it highlights the line:

Dim MyRst As DAO.Recordset

And for Bukhix's code it highlights the line:

Dim rsCOD As DAO.Recordset


Thanks for being so patient with me.

Dave

 

[bodhran1]

It works!!!!!

I stripped the troublesome line out (makes sense, right?) and it worked like a charm.

I owe ya both a pint.

Dave "knows enough to be dangerous" Pasquino

 

[BukHix]

You are probably missing the DAO reference so go to the Modules tab and Create a new module.

Once you have the code window open goto Tools > Reference (On the menu at the top) and make sure that MS DAO 3.5 is checked under the available references. If not check it and close the modules without saving anything. Retry the code and it should work.

 

[Rakier]

You should use the "Dim" line to declare the variable. You will need to add the proper reference like BukHix said. If you do not declare the variable (which by eliminating the line of code, you will not) then Access sees this as an undeclared variable and assigns the datatype of variant to it. Using a variant variable can cause system slowdowns when processing.

I'm also assuming that (since the code works without declaring the variable) you are not using Option Explicit in your general declarations portion of the code. Using option explicit will force you to declare your variables. Undeclared variables can cause alot of problems, especially given our innate abilities to produces typographical errors.

An example would be

Thisone = "Hi"
debug.print Thsone

It will not print anything as Access sees Thsone as a seperate variable from Thisone. Since you didn't declare the variable and are not using Option Explicit, it will not recognize that as a problem. It can be a big headache debugging code like this.

There is an option under Tools-Options that will automatically insert the Option Explicit into your code modules, thereby forcing you to always declare a variable. This is good practice and makes things alot easier down the road.

HTH

 

[bodhran1]

In the real version of my program I have declared all my values.

I never understood why I had to do it, but all the manuals I read tell me to, so I did.

Your simple example lets it all make sense.

VB is a blast to work with but at times can be sooooo frustrating. The help files are useless for a novice like me.

Maybe I'll stick with my day-job (Sr. PCB Designer).

Thanks again to all of you. Hopefully I can return the favor someday.


Dave

 

[shay]

Happy new year everyone!

Hi all

Sorry but did I miss something here?

The original question

I'd like the user to type in thier name, then when the "OK" button is clicked, I would like to write the name to a field in my table.

was answered by Rich but it seems that bodhran1 has opted to use code for this basic Access task.

Just wondered if there was particular reason?

shay

 

[BukHix]

He is probably writing data to a table separate from the one the form is bound to.

 

[bodhran1]

For those who are curious, here's what I'm doing:

Employees can suggest changes to our products, but there is a lot of information we require up front.

Who wants to make the change, which product, the reason, disposition of existing stock, affect on field units etc. Some users even need supervisors approval.

We may also want to go back and check the time it takes to complete a form or maybe how long it takes supervisors to approve them etc.

In most cases, it may take the user several sessions to complete the form, so when they close, I write the data to a temp file.

Once it is complete and/or approved it is promoted to the final table.

Hope this makes sense.

Another reason for using code. I had never used Access before I started this project. I knew VB a little bit, so I went with my strongest suit.

Thanks again for everyones help.

Dave