my code has a error !!!!

[toby0595]

(i will state first, i am a novice when it VBA so if the answer is simple i apologize now) as part of my as ict alevel we have to make a database and in that a form, within the form we are tasked with a producing a button. this button's function is to add a new yearly sub to the table. however after writing the code for i have been left with error saying "compile error: Expected: end of statement" . after looking over the form code i have no idea how to solve the error.


this is the code

Dim db = CurrentDb()
Dim rstMember As Recordset
Dim rstArchived As Recordset
Dim rstMemberSubs As Recordset
Dim rstMemberTrips As Recordset




Set db = CurrentDb()
Set rstMembers = db.OpenRecordset("Members")
Set rstArchived = db.OpenRecordset("archive")
Set rstMemberSubs = db.OpenRecordset("MemberSubs")
Set rstMemberTrips = db.OpenRecordset("MemberTrips")


Do While Not rstMemberSubs.EOF
If rstMemberSubs![Membership Number] = [Membership Number] Then
rstMemberSubs.Delete
End If


rstMemberSubs.MoveNext
Loop


Do While Not rstMemberTrip.EOF
If rstMemberTrip![Membership Number] = [MembershipNumber] Then
rstMemberTrip.Delete
End If


rstMemberTrip.MoveNext
Loop


Do While Not rstMember.EOF

If rstMembers![MembershipNumber] = [MembershipNumber] Then

rstArchived.AddNew
rstArchived.Fields("Forename") = [Forename]
rstArchived.Fields("Surname") = [Surname]
rstArchived.Fields("Address") = [Address]
rstArchived.Fields("City") = [city]
rstArchived.Fields("Postcode") = [postcode]
rstArchived.Fields("Year Joined") = [Year Joined]
rstArchived.Fields("Year Left") = Year(Date)
rstArchived.Update
rstMembers.Delete

Exit Do



End If

rstMembers.MoveNext


Loop


End Sub


thank you for any help

 

[pbaldy]

What line is highlighted? Only thing I notice right off is that your membership number field sometimes has a space; I'd expect it to be consistent. Far more efficient for your deletes:

Db.Execute "DELETE * FROM TableName WHERE MembershipNumber = " & MembershipNumber

 

[SteveH2508]

This is the current UK OCR exam board AS level ICT Structured Task - an examination question (which I happen to be teaching) (It is a b*gg*r of a task BTW). You do not need VBA code to accomplish this task. It can be achieved using an append query with a parameter.

Your code seems to be addressing the archive routine (a different task) which again, does not need VBA.

 

[DavidAtWork]

"If rstMemberSubs![Membership Number] = [Membership Number] Then"

What is this [Membership Number] and where is it getting it's value? Only I can't see any variable declaration, is it a value passed to this function as a parameter

David

 

[DavidAtWork]

also this line: Dim db = CurrentDb()
should be: Dim db as database

David

 

[LPurvis]

There's some weird part of me that finds it wonderful that there's an AS level ICT student being clocked by someone teaching the course.
If it's one from your own college/school.... Busted!!

That said (and my enjoyment put aside), I do agree. (With most things already said in fact.)
No apparent need for VBA here. A form parameter is fine.
Build queries and let them do the work. Become familiar with refering to forms controls as expressions.
Refer to Form and Subform properties and controls

Presumably, [Membership Number] is a control on a form you're using?
As would be [Forename], [Surname], ... etc.
I notice that you have both [MembershipNumber] and [Membership Number] in your code. You'll need to make sure which it is.
And also, for future reference of any coding you do need, it's better practice to prefix form references in code with the Me keyword to remove ambiguity.
e.g.
Me![Membership Number]
Me!Forename
etc.

 

[toby0595]

well thank you for the quick responses guys, i made the little changes to both

​

Dim db = CurrentDb()
and

​

[Membership Number] or [MembershipNumber]
thanks David and Paul

so how would i change the vba to a form parameter for both the archive button and subs button.

 

[LPurvis]

Well, I'm sure you'll appreciate that respondents in any quality forum would be about as like to give you the full answer as the teacher here (or your own teacher).
In any query in Access, an entry in it that's not a data object (table or field) is considered a parameter. If you use a form control expression - then that is evaluated for you by Access.

Try it yourself.
Create a new query for the Members table.
Under the MembershipNumber field (in a criteria row) try a form expression - something like:
Forms!FormName!ControlName
Where you have a form opened, which is named "FormName" with a textbox called "ControlName". Open the query, Access resolves the value entered in the form control as your criteria.

You could then, perhaps, apply this concept to other types of queries.
I feel like saying more, but I don't want to ruin the surprise.
Ah wait, that's from Se7en. I'm actually sure that's plenty for you to start with.

 

[tranchemontaigne]

To learn how to reference a form control in VBA I would recommend you open a blank query, open the expression builder and browse to the form and control you want. You will be using the expression builder to write your syntax correctly. copy and paste the syntax from the expression builder into your VBA module and you will be all set. Do this a few times and you will be able to reference the form control in code without need of the expression builder. When you get to referencing controls on subforms within subforms etc I would recommend you revisit the expression builder to help you learn the next level of referencing form controls.

 

[gemma-the-husky]

leigh

it's nice that Toby thanked the tutor for the pointer.

I think the thing is that with Access there is generally more than one way to swing a cat (pardon the metaphor)

Toby seemed to be trying hard, but with a few syntax issues - even if it was maybe not the way the tutors expected.

As your tutor says, the project is a tricky one to solve. Well possibly not but like many things, it probably does require a certain facility with (in this case) access that comes after years of practice.

indeed, I think access is unique in terms of "light-bulb" moments. Techniques that you would NEVER get yourself, but are blindingly obvious when shown.

good luck toby.

 

[LPurvis]

I agree wholeheartedly.
And that, without a mechanism specified, any solution is valid. (Even possibly one the course teacher isn't necessarily familar with!)
Toby was indeed trying hard, but with a good few syntax errors and concept grasps yet to come.
When struggling so much with coding in this case, a change of tack to something much simpler (as suggested by a teacher of that exact course) makes sense.
When learning, understanding is key. (Or you've not really learned.
As Carl Sagan said: If you want to make an apple pie from scratch, you must first create the universe. Learning VBA from scratch to perform a delete and insert - I think I'll start with some previously created farm apples and flour.

I share you wishes of luck to Toby. And that is any further stumbling blocks present themselves, they'll hopefully be brought up here.

Cheers.

 

[RainLover]

Way way back in my day, if you posted an answer that you had not been taught you would be marked down. Sometimes the exercise is to use the tools that you have been given rather than tools you obtained from other sources.

So if you answer in VBA when you have only learnt SQL you will be wrong regardless of the outcome.

Now aren't you happy I shared that useless bit of information with you.

 

[tranchemontaigne]

RainLover, I second your last comment. I was always marked down for coming up with the correct solution using methods that did not match what had been recently taught throughout my primary and secondary education.

<RANT>When I got to college I found that there was marked shift in tolerance for how problems were solved in that unorthodox methods with the correct outcome were accepted. (I was a math major - unorthodox approaches were considered educational in that they could lead to innovation.) The exception to this rule was the chair of the physics department always marked you down if you got the right solution, but used methods described in the text. He justified this position by refusing to discuss any topical information until AFTER the test. At that point he would teach his own methods and state that though the text book authors came up with the same solution, they are wrong and he would not grant more that half credit to anyone who got the correct answers using anything but his previously untaught methods. <\RANT>

 

[RainLover]

tranchemontaigne

The other thing I like abot this post is that people are teaching basics.

Things like Queries and the Query Grid. Then to finish it off the expression builder.

Sometimes I write code and can't see my fairly obvious mistake. So I use the query grid to point out where I went wrong.

Then there are the Tools. Like creating a search with a combo box is so simple using the toolbox.

One more thing happening here. People are teaching the OP how to learn. Nothing more annoying than someone jumping in with an answer before giving the OP the chance to work it out themselves.

Things would be a lot better all round if people were more reasonable in their approach and did things my way.

 

[toby0595]

This is the current UK OCR exam board AS level ICT Structured Task - an examination question (which I happen to be teaching) (It is a b*gg*r of a task BTW). You do not need VBA code to accomplish this task. It can be achieved using an append query with a parameter.

Your code seems to be addressing the archive routine (a different task) which again, does not need VBA.

so steve how does your school do the subs button and the archive button?

 

[toby0595]

This is the current UK OCR exam board AS level ICT Structured Task - an examination question (which I happen to be teaching) (It is a b*gg*r of a task BTW). You do not need VBA code to accomplish this task. It can be achieved using an append query with a parameter.

Your code seems to be addressing the archive routine (a different task) which again, does not need VBA.

so steve how does your school do the subs button and the archive button?

And thank's you every one for all the help