asked on

Method or data member not found

Hello, I am having problems figuring out why I can't get my form to submit registration information to a table in access database. I believe the problem in with the Inner Join.

The user should enter their student ID, and in populates the name and then they select the class from the combo box. The combo box lists the class and the number of slots available for a particular class. The submit button should grey out if the user selects a class that has zero slots available. If class is available it should submit to table.

tblRegistered is where the record should be added.

Any help resolving this would be greatly appreciated.

Thanks
TEST_FE.xlsm
TESTDB.accdb

John Tsioumpris

It seems that you have some issues
First
the correct declaration is :

rs.Fields("Remaining Slots")

Open in new window

Then an error pops due to missing End If
Proceeding i see that your rs is initialized as

src = "SELECT * FROM tblRegistered WHERE [studentID]= '" & Me.txtStudentNumber & "' AND [regClass]= '" & Me.cboClasses & "'"

Open in new window

later you change the src
but you don't change the recordset so it still points to the original src....no NO [Remaining Slots]
Hint : Target_DB better to have it like this :

Dim TARGET_DB As String  '(instead of constant)

Open in new window

And the Access Db in the Initialize event

TARGET_DB = Application.ActiveWorkbook.Path & "\TESTDB.accdb"

Open in new window

So that the Excel searches for the Access DB that is in the name Folder as the Excel...

John Tsioumpris

Edit..
here is the Excel...fixed...(changed src)
Just put it in the same folder with your Access or change back your Constant about Target_DB
TEST_FE.xlsm

B Blyden

ASKER

Hi John, thank you for assisting. It could be just user error, but I did look over the changes you made. While it says that record has been submitted, it actually doesn't record the information to the tblregistered. I even changed the where statement to include studentID. (See below). Am I missing something else?

src = "SELECT tblClasses.Classes, tblClasses.AvailableSlots, [AvailableSlots]-Count(*) AS [Remaining Slots] FROM tblClasses " & _
             "INNER JOIN tblRegistered ON tblClasses.Classes = tblRegistered.regClass WHERE [studentID]= '" & Me.txtStudentNumber & "' AND [regClass]= '" & Me.cboClasses & "'" & _
             " GROUP BY tblClasses.Classes, tblClasses.AvailableSlots"

Open in new window

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

Please don't forget to close our your question by identifying those responses which either solved your problem (This is my solution) or provided an assist (Not my solution, but helped),

B Blyden

ASKER

Thanks Again John. Your explanations are so easy to follow and understand.