Creating an Access 2000 form without Wizard - Code Only

[emajka21]

Hello, and thank you in advance for trying to help me. I am trying to create an access 2000 form without using the wizard. It just doesn't seem like I can the level of complexity I want out of the wizard so I want to hand code the form. If this was any other language accessing an access form I know how to do it but I am not sure how to access the tables WITHIN the access database itself.

Here is what I would like the form to do.
On Load:
Textbox1 - I want it to Select Year(field) From Year-Race (table)
Combobox1 - I want to Select Runnerid, LastName From Runner

After Load:
Textbox2 - I want to be able to enter a number
They also select the runners lastname/id
Year they can't change


I then have a button to save.

When this gets saved it needs to save these 3 pieces of info into a table called Race-Bib. There are only the 3 fields in this table (year, bibNum, runnerID)

I understand how to do SQL statements. The problem is connectivity. If this was any other language I would do something like:

Dim adoConn As ADODB.Connection
Dim adoRecs As ADODB.Recordset
Set adoConn = New ADODB.Connection
Set adoRecs = New ADODB.Recordset

AdoConn.open “database”
Or
adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "mydatabase.mdb'"


But since the database is already "open" because I am in it how to I reference my tables?

I have seen many examples that show to do the following (below) but when I try to run it doesn't like it. My only option appears to be adodb.whatever
Dim MyDB As DAO.Database, MyRS As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Mainframe Report List", dbOpenDynaset)

Is this not an option because I am still using Access 2000? I do have access 2003 available if that would be better.

I have programmed in numerous languages but programming within access just doesn't make sense to me.

To reiterate:
Tables:
Runner - Runnerid, FirstName, LastName, address...ect
Year-Race - Year, RaceNum
Race-Bib - Year, RunnerID, bibNum

Just to let you know bibNumbers change every year so to be able to keep track of historical data I am using the year/BibNum/Runnerid as a key in another table (Race Times)

I hope this is making some sense. Any help is greatly appreciated.

[MMcCarthy]

I'm a little confused about exactly what you are trying to do.

Have a look at the DLookup function. I think its more suitable for what you want.

[FishVal]

I understand how to do SQL statements. The problem is connectivity. If this was any other language I would do something like:

Dim adoConn As ADODB.Connection
Dim adoRecs As ADODB.Recordset
Set adoConn = New ADODB.Connection
Set adoRecs = New ADODB.Recordset

AdoConn.open “database”
Or
adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "mydatabase.mdb'"


But since the database is already "open" because I am in it how to I reference my tables?

I have seen many examples that show to do the following (below) but when I try to run it doesn't like it. My only option appears to be adodb.whatever
Dim MyDB As DAO.Database, MyRS As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Mainframe Report List", dbOpenDynaset)

Is this not an option because I am still using Access 2000? I do have access 2003 available if that would be better.

You can retrieve ADODB.Connection using CurrentProject.Connection property.

[emajka21]

I appreciate you answering me but I have given up on trying to use access. I have switched my project to C#/ASP as I am more familar with that language. Granted even that is being stubborn...