compile error: Method or data member not found (1 Viewer)

[pademo57]

Hi There,
Newbie here, trying to help out a school who have MS Access 2003. I usually program in C+.
They want a button to add a new record however I am getting the error mentioned in the title above.

The table shows:
DailyReportID autonumber (Primary Key)
fkStudCourID number (derived from another table for Student Course ID)
datDlyReportDate (should be obvious)
txtDailyCode text
numDailyNum number

But as soon as I click the button it says:

Compile error: Method or data member not found
What am I doing wrong? Here is the code:

Dim db As Database
Set db = CurrentDb
Dim rs As DAO.Recordset
With rs
            .AddNew
                !fkStudCourID = Me.fkStudCourID
               ...
                !numDailyNum = Nz(Me.numDailyNum, 0) + 1
                !datDlyReportDate = Date
                
            .Update
            
        End With

If I comment out that line everything else works?!?
Please help a Noob.

 

[Beetle]

If I comment out that line everything else works?!?

If you comment out which line?

 

[pademo57]

Argh! Sorry I really should read my post before I post it. I commented out the line:
!fkStudCourID = Me.fkStudCourIDremember you are dealing with a newbie so please be gentle!!!

 

[Beetle]

You are declaring a variable for a recordset, but no where in your code do I see where you actually open a recordset. I should look like;

Dim db As Database
Set db = CurrentDb
Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("tblYourTableName", dbOpenDynaset) [COLOR="Red"]'<<< open the recordset[/COLOR]

With rs
    .AddNew
    !fkStudCourID = Me.fkStudCourID
    !numDailyNum = Nz(Me.numDailyNum, 0) + 1
    !datDlyReportDate = Date
    .Update 
End With

rs.Close

 

[pademo57]

Oops, sorry, real noob here, I was going too fast when I copied the code. I did in fact put it in:

Dim db As Database Set db = CurrentDb Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tblDailyReport", dbOpenDynaset)
With rs     .AddNew     !fkStudCourID = Me.fkStudCourID     !numDailyNum = Nz(Me.numDailyNum, 0) + 1     !datDlyReportDate = Date     .Update  End With  rs.Close

 

[Beetle]

If it is only failing in this line;

!fkStudCourID = Me.fkStudCourID

then you should double check the spelling of name of that field in the table and the control (text box) on the form.

 

[spikepl]

I usually program in C+.

Therefore, if you know how to program, do not panic ...

1. You got a compilation error, in which case the offending line was in fact marked by colour
2. Access told you what was wrong - this is one of the sensible messages that Access offers (yes, it also has a plethora of brainless ones)- just think what it said.
3. There is a feature called Intellisense - whatever you type, type it slowly, character by character and pay attention to Intellisense offering choices in a popup - it is the interpreter(sort of compiler) checking what you type as you type it! It cannot catch runtime nonsense but can prevent the worst compilation errors.

 

[pademo57]

Thanks to Beetle and Spikepl for answering so quickly,
However, the weird part at least for me is when I try the intellisense it doesn't show fkStudCourID as an option although all the other fields are shown.
Is this because it is a foreign key and get its number from a different table?
Again, I have taken over from someone else and they had it set up like this:
tblStudent table |
| - connected to tblStudCour table
tblCourse table |

tblStudCour - connected to tblDailyReport
pkStudCourID (field) - connected to fkStudCourID (field)

The idea is that students and courses are a many-to-many relationship and each DailyReport is connected by the tblStudCour table.

I hope my crude diagram makes sense.
I'm almost to the point of giving up and re-writing the whole thing in C.
I really do appreciate the help!

 

[spikepl]

The Intellisense is telling you the same story - it has no clue about fkStudCourID. And I bet the machine will win this match !

The field fkStudCourID must be in the recordset of the form, or must be the name of a control on the form, or both. Intellisense and the error message are both telling you that none of this is fulfilled.

 

[pademo57]

OK I'm not sure what to do now. Maybe I will make a query to see what's up. Because I can go into the table and populate the field (fkStudCourID) but when I set up a recordset it doesn't see it.

I even tried copying the field name while I was in design mode from the table and then putting that name in the VBA code.

Thanks for your help, if I figure out what I have done wrong I will let you know.

Thanks again.

 

[gemma-the-husky]

we did this before did we not in another thread

!numDailyNum = Nz(!numDailyNum, 0) + 1

you need the bang operator ! on the RHS as well.


although this is actually meaningless, thinking about it
what are you actually trying to use as the new value for numDailynum

 

[pademo57]

If you could only see how much I am laughing about all this. Yes, thank you, gemma-the-husky, that worked.



However, now when I want to show to the new record, instead it goes back to the same record as before.

What is the code to show the new record once it has been created?

This REALLY is embarrassing having to ask all these questions.


(I should have just written this all over again in C)