How lost...well databases arnt my forte

Ok Yet Another Problem ..Bleh


Ok the following code "was" (cause it aint workin)
to assign a Competitor to an Event.

No here's the details.

Database created in access 97.
3 Tables

CompetitorTable
EventTable
CompetitorEvent

Competitor Attributes:
Competitor Number - Primary Key
Junk Etc

Event Attributes:
Event Code - Primary Key
More Junk etc

CompetetorEventTable Attributes:
Event Code - (meant to be some sort of link to event table)
Competitor Number - (meant to be some sort of link to competitor table)

Ok. Thats that
.
other details:

I've tried alternating primary keys, making them both primary keys, making neither primary keys etc.

EventTable.EventCode has a 1-To-Many-Relationship with CompetitorEventTable.EventCode

CompetitorTable.CompetitorNumber has a 1-To-many-Relationship with
CompetitorEventTable.CompetitorNumber

Anyway all this leads to is the following code wont work because
"You cant add a record or change a record because a related record is required in EventTable".

-The records are present in both other tables btw. This table is just supposed to relate the two :/

Code:

---


Dim sSelected As String
Dim sCode As String

sSelected = frmRegisterCompetitor.cboCompetitorNumber.Text
sCode = frmRegisterCompetitor.cboEventCode.Text

Dim DB As Database
Dim RS As Recordset

'Use the OpenDatabase function to open our database
  Set DB = OpenDatabase("E:\Assignment3\Games.mdb")

'Open the Contact table
  Set RS = DB.OpenRecordset("CompetitorEventTable", dbOpenDynaset)

 With RS
    'Set it to Add mode
    .AddNew
   
    'Enter the field values
    .Fields("EventCode").Value = sSelected
    .Fields("CompetitorNumber").Value = Val(sCode)
   
    'Update it
    .Update

    'Close it
    .Close
   
  End With

    Set RS = Nothing
    DB.Close
    Set DB = Nothing

End Function

---

Anyhelp is appreciated, maybe a run down on how i've got lost or am misunderstanding, and what statements it would accept instead would be good.

I can change the database in access if need be, but it is vital the tables are not expanded or reduced

Just need the info

Aren't your 2 strings (sCode and sSelected) the wrong way round when you assign them?

Code:

---


sSelected = frmRegisterCompetitor.cboCompetitorNumber.Text
sCode = frmRegisterCompetitor.cboEventCode.Text

---

then

Code:

---


.Fields("EventCode").Value = sSelected
.Fields("CompetitorNumber").Value = Val(sCode)

---

J.

Hi,

Don't think you need .value on the fields...

rs("fieldname")=value

Although you'll need the right types putting in.

Could I suggest Clng function for getting a long value from a string? (perhaps)

And I think Grantly is right

Regards
Vince

The error "You cant add a record or change a record because a related record is required in EventTable". is because there is a relation set up between the tables, so you need to either disable the relation or make sure the EventTable is fed the right data first.