Help with error 2448: You can't assign value to this object

[zombiezoom]

I need help with error 2448: You can't assign value to this object. What I am trying to do is allow user to select event id and race id from combo option. Once the user selects those, then the id associated with those is suppose to be carried over to a new form which should associated those two ids with runner id from runner table. Thus, I will have all three ids in event-race-runner table (which is an associative table). However when I do that I get the error message which does not let me insert the ids into this new table. I have attached the codes below. All id are number. The main table ids were generated automatically.

This is the code which should carry the ids:
Private Sub cmdAddRaceRunner_Click()
DoCmd.OpenForm "Input Runner for race", , , , acFormAdd, , OpenArgs:=cmbErEventid & "|" & cmbErRaceid
end sub

this is the form which should have assigned the id into the new table:

Private Sub Form_Load()
    If Len(Me.OpenArgs & "") > 0 Then
    Dim i As Integer
    Dim s As String
    Dim x As String
    Dim y As Integer
    Dim a As String
    Dim b As Integer
 
    s = CStr(Me.OpenArgs)
    i = InStr(1, s, "|")
 
    x = Left(s, i - 1)
    y = CInt(x)
    Me.txtAddRunnerEventid.SetFocus
    Me.txtAddRunnerEventid.Text = y
 
    a = Mid(s, i + 1)
    b = CInt(a)
    Me.txtInputRunnerid.SetFocus
    txtAddRunnerRaceid = b
    End If
 
End Sub
 

I have use extra variable because I thought I had to convert the text into integer before assigning. any help would be appreciated to solve the problem.

[patjones]

Hi zombie -

Just as a note for future reference, please use code tags when posting VBA...

Can you indicate what line the error is happening on? You can do this by setting a breakpoint in the code and then stepping through it line by line using the F8 button, until the error gets triggered.

Looking at your code, I see one possibility for the error, but I'd like to confirm first that it's where Access is stopping before I point it out.

Pat

[missinglinq]

What exactly is the purporse of the "|" in your code?

Welcome to Bytes!

Linq ;0)>

[RuralGuy]

Here is a simpler way to do what you are doing. http://www.baldyweb.com/OpenArgs.htm

[zombiezoom]

@zepphead80
Thank you for your response. I am new to VBA and am not sure what exactly you mean by tags.

I get error when I try to assign values into text fields "Me.txtAddRunnerEventid.Text" and "txtAddRunnerRaceid". The text fields are part of an associative table (event-race-runner table). What I am trying to do is assign the value that I extract from a prior form and insert into the event-race-runner associative table - thus inserting eventid and raceid. For some reason, access is not allowing me to input that values as it gives me error 2448: You can't assign value to this object. To get around the problem, I tried to use VBA instead of macro from in the previous form save button. But even in that I get error message saying that I am an error in the INSERT STATEMENT. The following is the SQL INSERT command I used:

CurrentDb.Execute "INSERT INTO event-race-runner (eventid, raceid, runnerid) VALUES (txtAddRunnerEventid, txtAddRunnerRaceid, txtAddRunnerRaceid)"

The above SQL command also includes a runnerid that is autogenerated.

[zombiezoom]

@RuralGuy
Thank you. This is definitely lot easier and I am using this instead of my old VBA code.

[zombiezoom]

@missinglinq
I use it to separate the arguments that I am passing from one form to another.

[RuralGuy]

You're welcome. Glad we could help.

[zombiezoom]

Hi RuralGuy,

I am still unable to solve the error message and I could really use some help. Here is additional explanation about my error:

I get error when I try to assign values into text fields "Me.txtAddRunnerEventid.Text" and "txtAddRunnerRaceid". The text fields are part of an associative table (event-race-runner table). What I am trying to do is assign the value that I extract from a prior form and insert into the event-race-runner associative table - thus inserting eventid and raceid. For some reason, access is not allowing me to input that values as it gives me error 2448: You can't assign value to this object. To get around the problem, I tried to use VBA instead of macro from in the previous form save button. But even in that I get error message saying that I am an error in the INSERT STATEMENT. The following is the SQL INSERT command I used:

CurrentDb.Execute "INSERT INTO event-race-runner (eventid, raceid, runnerid) VALUES (txtAddRunnerEventid, txtAddRunnerRaceid, txtAddRunnerRaceid)"

The above SQL command also includes a runnerid that is autogenerated.

[RuralGuy]

To start with, controls have both a .Text property as well as a .Value property. The control needs to have the focus if you use the .Text property but not if you use the .Value property (the default). I mostly use the .Value property and Me.ControlName.Value and Me.ControlName both will use the .Value property.

[RuralGuy]

Is event-race-runner a TABLE or a QUERY?

[zombiezoom]

event-race-runner is a table.

[RuralGuy]

Are those values the same values passed in the OpenArgs argument and is the event-race-runner table the RecordSource of the 2nd form?

[zombiezoom]

@RuralGuy
Yes, the values passed on from OpenArgs should give me eventid and raceid. However, the runnerid is autogenerated value coming from runner table. What I am trying to do here is input all three ID values into event-race-runner table. To do so, I wanted to use values in text boxes and insert into the event runner table. I tried using the following SQL command but I get an error message saying I have incorrect INSERT statement.

CurrentDb.Execute "INSERT INTO event-race-runner (eventid, raceid, runnerid) VALUES (txtAddRunnerEventid, txtAddRunnerRaceid, txtAddRunnerRaceid)"

[RuralGuy]

What is the RecordSource of your 2nd Form?

[zombiezoom]

@RuralGuy
I am not exactly sure what you mean by RecordSource. However I selected the form that I am trying to input all data and copied and pasted the RecordSource. Hopefully this is what you are looking for. If not, please let me know. Sorry, but I a newbie to ms access and vba. Thanks for your help.

here is the RecordSource:

SELECT runner.runnerid, runner.firstname, runner.lastname, runner.streetaddress, runner.city, runner.state, runner.zipcode, runner.email, runner.phone, runner.dateofbirth, runner.age AS Expr1, runner.gender, runner.shirtsize, [event-race-runner].eventid, [event-race-runner].raceid, event.eventname, race.racename, [event-race-runner].runnerid AS [runnerid_event-race-runner] FROM runner INNER JOIN (race INNER JOIN (event INNER JOIN [event-race-runner] ON event.eventid=[event-race-runner].eventid) ON race.raceid=[event-race-runner].raceid) ON runner.runnerid=[event-race-runner].runnerid;

[RuralGuy]

The RecordSource you posted is a query (excellent) that joins the [runner] table with the [race] table and the [event-race-runner] table. It is possible this query is not updateable. Try opening the query by pressing the "..." button on the RecordSource line of the property sheet and see if you can change anything. If it just beeps then the query is not updateable. http://allenbrowne.com/ser-61.html

[patjones]

Hi -

Rural Guy is giving some great insight here.

Are the text boxes that you are assigning the values to bound or unbound? For a little explanation on the difference, see read the first couple of paragraphs of the following page:

http://www.igetit.net/newsletters/Y0...undUBound.aspx

Pat