Update Subform recrods based on txtbox on parent form

[GaryS]

I have a form bound to tblModels and a subform bound to tblModelDetails. For
each record in tblModels there may be 8 to 10 records in tblModelDetails.
The subform is a continuous form with several text boxes. Some of the text
boxes require repetitive input, and for those I have placed textboxes on the
parent form so that the data can be entered and then the suborm records
updated by code in a command button. My problem is I don't know what code to
put in the command button.

Any help would be greatly appreciated.

 

[Steve Schapel]

Gary,

Here's one approach...
CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET AField=" & Me.ATextbox & "," & _
" BField ='" & Me.BTextbox & "'," & _
" WHERE ModelID=" & Me.ModelID, dbFailOnError
(this example assumes AField is number and BField is text).

 

[GaryS]

Steve,

That's exactly what I need except it doesn't work. I get a syntax error
message in update statement and when I click debug it shows the failure in
the last line of the code, failure #128. Here is the code

CurrentDb.Execute "UPDATE tblModelDetails" & _
" CapRed ='" & Me.txtCapRed & "'," & _
-> " WHERE ModelID=" & Me.ModelID, dbFailOnError

When I hover the mouse over modelID it does show the correct model #, which
by the way is a text field.

Any other thoughts? Thanks so much for your help.

 

[Steve Schapel]

Gary,

Do you have a ModelID field? I mean I guessed right?

You have omitted the SET from the code, and also there should be no
comma if you are only updating one field, so try it like this...
CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed ='" & Me.txtCapRed & "'" & _
" WHERE ModelID=" & Me.ModelID, dbFailOnError

 

[GaryS]

Oops! I'm sorry. When you wrote text field or number field I thought you
were referring to ModelID (yes, you guessed correctly, there is a modeID
control on the parent form as well as the sub). This field we are trying to
update is a Currency field, although I have text controls that I want to set
up in the same manner. It still gets the same error. This is code I tried
for updating CapRed. What have I screwed up?

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & "," & _
" WHERE ModelID=" & Me.ModelID, dbFailOnError

Thanks again for your help.

 

[Steve Schapel]

Sorry, Gary, again no comma. Not sure if that is the problem, but
everything else seems ok. Try...

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE ModelID=" & Me.ModelID, dbFailOnError

 

[Dale Fye]

Gary,

If the ModelID and the value in your txtCapRed control both contain strings
rather than numerics, you will need to wrap them both in quotes.
I saw a neat trick tonight on the forum for wrapping stuff in quotes, so
bear with me. Create the following function and put it in a code module:

Public Function Quote(text as string) as string

Quote = chr$(34) & text & chr$(34)

End function.

Now your code should look like:

CurrentDb.Execute "UPDATE tblModelDetails" _
& " SET CapRed = " & Quote(me.txtCapRed) _
& " WHERE ModelID=" & Quote(Me.ModelID),
dbFailOnError

HTH
Dale

 

[GaryS]

Dale and Steve

Dale - Your idea of using the function is neat, but for now I have just
included the &Chr(34) which I don't think I'll ever get the hang of <g>.
Here is the code that works!!!!!!

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= " & Chr$(34) & Me!ModelID & Chr$(34)

Forms!frmModelinfo![tblModeldetails subform1].Requery

Steve and Dail - Thank you very very much!!!!!

 

[Steve Schapel]

Gary,

Ah, so ModelID id text. Sorry, I overlooked that possibility!

In that case, for what it's worth, I would prefer this syntax ...

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= '" & Me.ModelID & "'"

 

[GaryS]

Steve,

This is perfect and working great, but now what if I wanted to just clear
the contents of the field in the subform. This particular filed [Message] is
a text field. I have tried variations of the following:

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET Message=" Null _
" WHERE [ModelID]= '" & Me.ModelID & "'"

I can update [Message] without any trouble, but this new code would be in a
seperate command button labled "Clear".

Thanks for your awesome help.



--
Gary in Michigan, USA

Gary,

Ah, so ModelID id text. Sorry, I overlooked that possibility!

In that case, for what it's worth, I would prefer this syntax ...

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= '" & Me.ModelID & "'"

--
Steve Schapel, Microsoft Access MVP

Dale and Steve

Dale - Your idea of using the function is neat, but for now I have just
included the &Chr(34) which I don't think I'll ever get the hang of <g>.
Here is the code that works!!!!!!

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= " & Chr$(34) & Me!ModelID & Chr$(34)

Forms!frmModelinfo![tblModeldetails subform1].Requery

Steve and Dail - Thank you very very much!!!!!

 

[Michel Walsh]

Hi,


CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET Message= Null " & _
" WHERE [ModelID]= '" & Me.ModelID & "'"



You were missing operators around the constant NULL, and concatenation with
NULL ... concatenates nothing. That is why the constant has to be supplied
inside the string itself.

That assumes Message field allows NULL, by design of the table.


Hoping it may help,
Vanderghast, Access MVP

Steve,

This is perfect and working great, but now what if I wanted to just clear
the contents of the field in the subform. This particular filed [Message]
is
a text field. I have tried variations of the following:

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET Message=" Null _
" WHERE [ModelID]= '" & Me.ModelID & "'"

I can update [Message] without any trouble, but this new code would be in
a
seperate command button labled "Clear".

Thanks for your awesome help.



--
Gary in Michigan, USA

Gary,

Ah, so ModelID id text. Sorry, I overlooked that possibility!

In that case, for what it's worth, I would prefer this syntax ...

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= '" & Me.ModelID & "'"

--
Steve Schapel, Microsoft Access MVP

Dale and Steve

Dale - Your idea of using the function is neat, but for now I have just
included the &Chr(34) which I don't think I'll ever get the hang of
<g>.
Here is the code that works!!!!!!

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= " & Chr$(34) & Me!ModelID &
Chr$(34)

Forms!frmModelinfo![tblModeldetails subform1].Requery

Steve and Dail - Thank you very very much!!!!!

 

[GaryS]

Ok, I thought I had tried that variation but I guess I hadn't. Thanks so much!
--
Gary in Michigan, USA

Hi,


CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET Message= Null " & _
" WHERE [ModelID]= '" & Me.ModelID & "'"



You were missing operators around the constant NULL, and concatenation with
NULL ... concatenates nothing. That is why the constant has to be supplied
inside the string itself.

That assumes Message field allows NULL, by design of the table.


Hoping it may help,
Vanderghast, Access MVP

Steve,

This is perfect and working great, but now what if I wanted to just clear
the contents of the field in the subform. This particular filed [Message]
is
a text field. I have tried variations of the following:

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET Message=" Null _
" WHERE [ModelID]= '" & Me.ModelID & "'"

I can update [Message] without any trouble, but this new code would be in
a
seperate command button labled "Clear".

Thanks for your awesome help.



--
Gary in Michigan, USA

Gary,

Ah, so ModelID id text. Sorry, I overlooked that possibility!

In that case, for what it's worth, I would prefer this syntax ...

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= '" & Me.ModelID & "'"

--
Steve Schapel, Microsoft Access MVP

GaryS wrote:
Dale and Steve

Dale - Your idea of using the function is neat, but for now I have just
included the &Chr(34) which I don't think I'll ever get the hang of
<g>.
Here is the code that works!!!!!!

CurrentDb.Execute "UPDATE tblModelDetails" & _
" SET CapRed=" & Me.txtCapRed & _
" WHERE [ModelID]= " & Chr$(34) & Me!ModelID &
Chr$(34)

Forms!frmModelinfo![tblModeldetails subform1].Requery

Steve and Dail - Thank you very very much!!!!!