Replies

Rob

One quick way of checking whether there is ‘rogue’ data in a worksheet is to click CTRL-END. If the selected cell is outside the area to be imported, you will need to delete some rows and/or columns. You must save the workbook after the deletion, and then try CTRL-END again. When CTRL-END selects the bottom right corner of the data to be imported, then you can proceed with the import.

Nick

Hans

Thanks for that. I added the code, but the problem was still there.

I did some more investigation on the user’s PC, and it appears there is a problem with the Access installation – she cannot create any reports at all, even on 1 table databases. Hopefully when Access has been re-installed the problem will be solved.

Nick

Federico

If you could link the tables to SQL Server, then you could try the following method. Without knowing your exact setup this may not be entirely appropriate, but you could :

Set up a table in SQL Server which will contain your parameters (v1 and v2, etc) and a blank column for the result.

Create a function/sproc in SQL Server which will populate the blank column with the correct results

Make sure all the 400 or so rows in Excel are correctly populated with the parameters.

Have a command button which will run the code which will :

Copy the Excel sheet into the SQL Server table using ADO

Run the SQL function to poulate the results column.

Copy the fully populated table back into Excel.

This should be fast – you will only need a single connection

HTH

Nick

Alan

In my experience, the easier way of doing this is to use Excel 2003. Click DataImport External DataNew Web query. Put in your URL, select the area of the data you need & it will download into the worksheet. You can then paste this into your Access table.

If you know enough VBA, you can automate this process. I can supply some code if you are interested.

Nick

Luke

I don’t think you can nest tab controls. You would be better off creating a subform, inserting the mini tab control on that, and then inserting the subform on the main tab control.

HTH

Nick

Hans

Thanks for that.

I managed to find something similar in the meantime which I ran after the first loop:

For intLoop = 0 To intNumFields – 1
Set txt = frm.Controls(intLoop)
Select Case intLoop
Case 0
‘Do nothing
Case 1
txt.TabIndex = intNumFields – 1
Case Else
txt.TabIndex = intLoop – 1
End Select
‘txt.Name = strName
Next

Thanks again

Nick

Rudi

I added StartDate and Enddate to the courses table to make this work.

If you run the query qselCountDup in the revised attachment, and put in the DelID (number), start and end dates for the proposed course you will get back the number of overlapping courses.

Nick

Rudi

Here’s a simpler version. It assumes delegates attend each day of a multi-day course, and that the course table contains a start and end date for each course.

Once you know the delegate ID, and the proposed start and end dates of the course they want to book, run this query which takes 3 parameters:

SELECT COUNT(*) FROM tblJunction j INNER JOIN tblCourse c
ON j.CourseID = c.CourseID
WHERE (([ProposedStartDate] = c.StartDate) OR ([ProposedEndDate] >= c.StartDate AND [ProposedStartDate] 0 then the delegate’s course dates overlap.

Nick

Rudi

OK. In the case of courses running over more than one day I think the solution would be a new table DelegateBookedDate – this would contain one row for each day of each course for each delegate (columns would be DelID + CourseDay). This would be populated (using an Append query) when the delegate is booked on a course. You would need to add to the booking procedure a check that for a new booking for a delegate none of the days of the new course already appear in the DelegateBookedDate table.

HTH

Nick

Rudi

I think if you add the course date to the junction table and add that to the PK this should solve the problem.

Nick

Steve

A further thought. I don’t know whether this would be applicable to your particular setup, but FWIW you could think about using VMWare Server (at vmware.com) to create a virtual PC, and run Office 2003 on your normal Pc, and Office 2007 on your virtual PC. It’s free for personal use.

HTH

Nick

Steve

I think you need to set the value in code when you open the calendar form.

In the Open event you can add :

frmCalendar.calCalendar.Value = Date

changing the form and calendar object names as appropriate

Nick

Steve

I am running Access 2003 and 2007 on the same PC. I do get the installation process the first time only when I run 2003 after 2007 (or vice-versa), but only the once.

I have never had 2007 crash on my PC – are you running 2003 and 2007 at the same time ? Have you tried repairing the 2007 installation ?

Nick

It might also be worth trying a decompile – this has helped me out more times than I care to remember :

http://www.trigeminal.com/usenet/usenet004.asp%5B/url%5D

Nick

Something like this should do the trick :

Forms(“yourform”).Move _
Left:=0, Top:=0, Width:=400, Height:=300

HTH

Nick