frame with radio buttons 1

[GShen]

Hi, I have a select query with multiple tables joining which are populating a form. I added an frame with 3 radio buttons. There is a table in the select which has the type which can be either a 0,1 or 2, hence the 3 buttons. I have the frame on the form as an unbound field. I can click on the frame obviously and change the radio buttons which I will kick off a stored procedure (after update) to update the assoicated field and refresh the screen. I can do all of that. Howeve, how can I display the value of the field in the frame from the select? If I set it to the control, then I can't update the field obviously. I tried DEFAULT and I tried ON OPEN to set the frame to the field but both do not work. This is a continues form by the way. There has to be a way of getting around this.
Thanks in advance

Remember when... everything worked and there was a reason for it?

 

[MajP]

Sorry it can not be done in a continous form. Since it is an unbound control, by changing its value all the visible controls will also change.

In a single form view you can do it on the form oncurrent event
me.frameName.value = me.somefieldName

 

[PWise]

have the control bound
add an event to the mouse down and move the after update to the mouse down event

 

[GShen]

Majp, That is exactly the problem. Everything gets set to the same value.
PWISE - Not sure of what you are saying. If I set the frame control source, the frame is set to the value but I cannot change anything on the form because it is bound to that field and you can update anything with mult. tables in a select. You said mouse up or mouse down but what would that do for me? How would they be able to change the radio button to the new value?

Thanks everyone but it looks like I have to try something else.

Remember when... everything worked and there was a reason for it?

 

[MajP]

I might be a little confused. If you are actually saving in the values 0,1,2,3 you can bind the option group and no code is needed.

Rarely are people saving the integer but using the frame in a select case to store another value. But you may be able to bind it.

 

[PWise]

is the query behind the for updateable??

 

[GShen]

I have a field being returned from a stored procedure which has 6 joins on it populating the form. This field contains a 0,1 or 2.
I have an unbound frame on the form with 3 options. 0, 1 and 2. When I click on the option, I will fire off an sp to update the field to the new value. The problem is I want the form to show the proper radio button highlighted for each record on the form.
Is that any clearer? Thanks.

Remember when... everything worked and there was a reason for it?

 

[PWise]

Why can"t you bind the control to the field

 

[GShen]

Do you mean set the control to the field which I am bringing back? I can do that easily. However, I cannot update that field at that point without creating some other or what have you to update the new value.

Remember when... everything worked and there was a reason for it?

 

[PWise]

Then the query behind the for is not updateable!

So let us do this bind the control to the field from the query

and instead of running you stored procedure on the after update of the frame run it on the mouse down event of each radio buttons

 

[GShen]

PWise, Either I am losing it as it is getting late in the day or we are on different pages. You are telling me to set the control to the field. Ok, 1st problem fixed. Now you want me to use the code for the mouse, move up or down. I need them to click on the radio button. There are 3 of them. One is highlighted witht he value which it is binded to. I need to be able to click on any of the other radio buttons and have it change the value. I don't understand how I can do that if I bind the the control from the query. As soon as you do that, the form is read only and you cannot update anything. Standard ACCESS stuff. The only way I can do this is to display the field with the current value(which I am out of room on the form) and then have the radio buttons as I have them (not binding) and change them and update the existing field in an SP after update. Does that make any sense ?



Remember when... everything worked and there was a reason for it?

 

[PWise]

dont know just created a form that the record source was not updateable

create a frame with 3 radio buttons bound the frame to the field from the qurey

put this code on the mousedown


Private Sub Option5_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
CurrentDb.Execute "update digits set digitid = " & Me.Option5.OptionValue & " where id = " & Me.id
me.requery
End Sub

and it works

 

[GShen]

Good Morning PWise,
I am good with frame. Good with the binding. Now the mouse down.
I put the code in on all 3 options. I have option76,78 and 80. in "update digits set digitid = " & Me.Option5.OptionValue & " where id = " & Me.id.
What fields are resvered words and which do I need to change? Is digitid my framename? Is me.ID my framename? What is where ID?
Obviously option 5, needs to be chagned to my 3 options for each mouse down event of each option. Thanks Pwis. So far it is working as the code is executing. Just need to get it to update now.


Remember when... everything worked and there was a reason for it?

 

[PWise]

What code did you have in the after update to kick off the stored procedure

 

[GShen]

PWis,
I sort of just figured this out while reading it again. I changed to CurrentDb.Execute "UPDATE tblVendorAR set tblvar_FlaggedForDeductionType = " & Me.Option80.OptionValue & " where tblvar_ID = " & Me.tblvar_ID

It is basically and SQL update. Didn't realize that. Still getting Error code 91. object variable or with block variable not set. Do I have to set currentdb to anything ?

Remember when... everything worked and there was a reason for it?

 

[PWise]

What s the back end of this database access or Sql

what does this give you

debug.print "UPDATE tblVendorAR set tblvar_FlaggedForDeductionType = " & Me.Option80.OptionValue & " where tblvar_ID = " & Me.tblvar_ID

 

[GShen]

backend is sql. I executed your command and it gave me back.
UPDATE tblVendorAR set tblvar_FlaggedForDeductionType = 2 where tblvar_ID = 211332
That is exactly what it should be. If I run that code in sql it updates fine. I could always kick off a stored procedure to do the update and pass the parameter for the option. I never did it like this before. This looks more efficient. That code would be something like the following.
Conn.Execute "execute spVendorFlagOpenAR @VendorARID = " & Me.tblvar_ID & _
", @FlaggedType= '" & Me.Option80.OptionValue & "'"

Remember when... everything worked and there was a reason for it?

 

[PWise]

is this an adp or mdb

 

[GShen]

adp

Remember when... everything worked and there was a reason for it?

 

[PWise]

in adp currentdb does not work

Option Compare Database
Option Explicit
Public Cnn As New adodb.Connection
Dim cmd As New adodb.Command
Function InitializeAdo()
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open CurrentProject.Connection
End If
End Function
Function ExecuteAdo(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams())
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim A As Integer
InitializeAdo
With cmd
    .CommandText = AdoString
    Set .ActiveConnection = Cnn
    .CommandType = adoCommandType
    .CommandTimeout = 0
    For Prams = 0 To UBound(AdoPrams)
        .Parameters(Prams) = AdoPrams(Prams)
    'Debug.Print .Parameters.Item(Prams).Name, Prams, .Parameters.Item(Prams).Value
    Next Prams
    .Execute A
End With
ExecuteAdo = A
End Function

paste this into a model

and use like this

call ExecuteAdo("spVendorFlagOpenAR",4,[COLOR=red]0[/color],Me.tblvar_ID ,Me.Option80.OptionValue)

note use the zero only if you have a return value in you Sp