Reading through the pre-release for Unit7 I am concerned that students are being asked to create a web front-end to the database. A skill that the students are not asked for in this unit. The notes call for an online reservation.

Does anyone else have this feeling?

I don't really see how they can do this if it is not specified in the unit specification, which it is not. I think the intention is to simulate the web entry forms in Access. My concern is more with the complicated user interface it is asking for. Just generating the 16 digit card number and ensuring that the 8 digit customer number is unique and one more than the last one is giving me a headache. If they supply datafiles with existing card numbers in it and you import those to the table how do you generate a new one that is one more than the existing highest? You can't rely on autonumber for this.

Reading through the scenario, I don't think the data structure, validation etc is going to be overly complicated. The 16 digit number shouldn't be much of a problem and you can use an autonumber. The 1st 8 digits are always the same (5555 1234 or something like that). If you use the Format of a number field you can specify how it appears eg 0000 0000 will display an 8 digit number with a space in the middle. The format "5555 1234 "0000 0000 would do similar but display the 16 digit code as required. Have a play and see how it works.

There is another technique you can use which uses queries to lookup the MAX value. You would then add 1 to this and append the value. If you are worried about storing the 16 digit code you could always use text field and a bit of text manipulation to store the required value.

My main concern is with the interface the students are required to produce.

I have sent a request for more Info on this to edexcel ask the experts panel.

 If you put the Field headings into an new empty  table  and make the  Member ID  an Auto number then import  to this table  it will work. I have spent days trying to work out  a way of creating the user interface  and have made no progress . Any ideas?

The problem with using the autonumber data type here is that the member file will no doubt have memberIDs already assigned. You will not be able to import these and so any relationships that need to be created will not work correctly.

I'm not quite sure what you mean here Andy. I have successfully imported a file that has member IDs of the format "00000000" (i.e. text) into a table that has member ID as autonumber. I then display the card number in 4 text box controls that have their control source set to ="5555", ="1946", =Mid(Format(CStr([MemberID]),"00000000"),1,4), =Mid(Format(CStr([MemberID]),"00000000"),5,4).

 I don't know about anyone else but this scenario seems more like a test of the teachers' skills than the pupils'.

I am with you on this onw. Was at an EDEXCEL meeting on Wed where this came up.  Others also had concerns so I have sent something to the guy who was running the day,  He is going to contact the Head guy.  I am also concerned about the ID and the error message as this is VBA I think.  Do not feel auto number field will do.  More info soon I hope. 

I stand corrected. It would appear you can append memberIDs to an autonumber field. I'm sure this didn't work with earlier versions. Surprisingly enough i found that I can even append records, delete them and then reappend without any errors being caused or changes to IDs.
The string handling is the route I would take too in order to add to preceding code. Not sure that I would have thaught to divide it up into different text boxes. I would just format it with the space included.

The other 2 exams have been quite taxing. My students last year sat both sessions. Interesting to see the solutions they came up with after the exam window had closed.

Right, I am stumped. I have managed to get the forms working, registering a new member and then displaying the card number. The bit I can't figure out is displaying an error message when they enter a non existent card number. It has to be something to do with a query returning 0 rows but how do you find out if a query has returned 0 rows and then how do you display a different form in this case? I didn't realise that this level of programmatic functionality was going to be necessary. Or are we barking up the wrong tree and they will only ask for the individual forms to be created and not expect the interactive functionality of the forms? Hmmm.

The only solution I have found to this so far is to insert a text box on the form. Using the form load event me.recordsource.recordcount is pushed into this control and the value interegated for 0 value. Of course the text box can be missed out but is useful for debugging. I would consider that anything other than code produced by the wizards and adaptations to it is outside of the scope for this unit. This is moving into programming, NOT using DB software.

What do you think?

I think that this is more like a scenario for unit 12 Customising Applications. I'm thinking now that the exam might only ask the students to create some of the forms necessary for the system and not come up with a fully operational system but we can't rely on that of course. Which property on the text box do you put the me.recordsource.recordcount ?How do you then display a different form when the recordcount is 0?

The recordcount cannot be accessed directly from the form, only in code. to use it I placed a text box on the form. itself it has no controlsource. I used the Onload event of the form to make the text box = me.recordsource.recordcount.
Didn't get very far with this as a solution.

This is another idea. I have adapted the code from a command button used to open a reservations form and used a DCOUNT function to establish if the membershipNo exists. If it does then reservation form opens otherwise new member form opens.

DoCmd.OpenForm "MainReservationForm", , , stLinkCriteria
Else
DoCmd.OpenForm "Form1", , , stLinkCriteria
End If

This is now moving into the realms of UNIT12.

There is always the idea that if the membershipNo does not exist the main form will be blank when it opens, allowing New membership details to be entered. The students may also be asked to create something for the administrator users rather than members.

Did you get a response from the 'Ask the experts' panel? I have just sent a message to ask if the use of VBA is expected in the exam as it is not in the unit spec.

I did get a reply from Ask the Experts. It was as curt as you would expect.

"Students will not be expected to create a web interface.  They will be instructed to create the interface using standard forms."

I have been working on the 16 digit number but cannot seem to get the required result have tried to use the max function to add 1 on, which has worked.  When I try and conbine it with the rest of the 8 text digits it looses the none sig zeros. 

 can get 0000001 for the ID but when I combine the two bits I get 555519461, any ideas

How have you solved it.  Hav also been working on the interface and seem to be making some progress.  Feel it is still a bit hard for the kids, am going to try and get a message to EDEXCEL about it.