Looking for some help

[rkrause]

On my form(unbound) i have about 3-4 fields that I need updated and am only allowed to update these few fields. I am thinking about creating an update button that will run 2 update queries and pass custId as a variable. could someone give me a little more help, i am not sure where to really go from there.

 

[edtab]

Using an update button is fine, but what values do you want
in the four text boxes and where are the values coming from?

Are you updating your tables based on the values you input
in the 4 text boxes? You need to provide more details.

 

[rkrause]

edtab,

the values in the text boxes will be coming from input from a user, and the tables will be updated based on the users input.

pat,

I am taking over a program that a user before me created. He used unbound forms and lots of code.

 

[edtab]

One approach you could use is to create a VBA code in the after update of each of the 4 text boxes on your form.

You could use the following syntax:

Docmd.RunSQL "UPDATE TableName SET TableField = Me.TextboxName WHERE CustomerID = " & Me.CustomerID

Substitute your own table name, field names and textbox names ... I think you can see the gist of the above code.

Try this and let me know how it went.

 

[rkrause]

Once I have the code in the after update on the textboxes, what code do i put in the update button on click event.

Thanks

 

[rkrause]

edtab any help

thanks

 

[WayneRyan]

rk,

Pat is right. You should try to migrate the application to bound forms.

In the interim (note the punctuation):

DoCmd.RunSQL "Update YourTable " & _
             "Set    [Field1] = '" & Me.SomeTextField   & "', " & _
             "       [Field2] = "  & Me.SomeNumberField & ", " & _
             "       [Field3] = #" & Me.SomeDateField   & "# " & _
             "Where  [PrimaryKey] = '" & Me.SomeStringPK & "'"

hth,
Wayne

 

[edtab]

Pat and Wayne's suggestions to use a bound form is the way to go.
It is certainly easier. However, if you still want to continue using your
unbound form, then my suggestion of running an SQL statements would certainly work.

You can place the DoCmd.RunSql statements in the click or double click
event of the command button instead of the after update events of the text boxes.

Pay particular attention to the syntax especially the WHERE clause.

If your primary key is numeric, then, the syntax would be:

"Where [PrimaryKey] = " & Me.YourTextBoxName

Most primary keys I have encountered are numeric.


However, If your primary key is text, the syntax would be:

"Where [PrimaryKey] = '" & Me.SomeStringPK & "'" as Wayne has indicated.

caveat for apostrophes:

("Where [PrimaryKey] = """ & Me.SomeStringPK & """'" if you are dealing with apostrophes like O'Brien for example)

--- 3 double quotes immediately to the left of the first ampersand(&) and after the = sign ..then 4 double quotes to the right of the last
ampersand. Single quotes would not work here as Access would get confused with the apostrophe in O'Brien.


Hope all the responses you got had helped you to some degree.