K
Karen
I am through reading the books! I need some help. Please
(begging).
I have two tables.
table1 is an excel link from a sheet that is a grid (14
columns x 7 rows). The columns are labeled numerically
and the rows are labeled alphabetically hence, a location
is given for each cell of the grid (i.e. 7B). Inside the
grid are numbers associated with vehicles of different
lengths. The lengths are 30, 35, and 40.
On a second sheet of the Excel workbook the grid is
arranged so that it is in 3 columns, it is this sheet
(ParkingLink) that is linked to Access.
column A is the grid location
column B is the associated vehicle number
column C is an If statement that determines the vehicle
size (based on vehicle number).
table2 is a non-linked table that is in Access. It
contains 3 columns: RouteId, VehicleSize, and Time.
I am trying to write code with a For Next Each that will
assign a vehicle to a route when the following is true:
1. the vehicle is first in the row (so when one has been
assigned it cannot be assigned again- the next vehicle in
line is used)
2. the vehicle in the grid is equal to the VehicleSize of
table2
3. the vehicle is assigned based on Time of table2
For example:
1A (grid#) = 35 (vehiclesize) = 600 (time) = 3103
(vehicle number)
1B (grid#) = 40 (vehiclesize) = 630 (time) = 5201
(vehicle number)
1C (grid#) = 35 (vehiclesize) = 600 (time) = code needs
to pick next column for a match in size/time because 1B
is assigned to 630.
This is what I did: I made 3 queries.
query3 has fields: VehicleNumber, VehicleSize
query2 has fields: GridId, VehicleNumber
query1 is a relationship from queries 1 and 2 so that
VehicleNumber has the GridId and VehicleSize.
I have a fourth query (query4) from table2 with fields:
RouteID, required VehicleSize and associated Time. There
are 26 records in this table/query and 24 records in
query1. Otherwise I would just join query1 and query4
together based on VehicleSize but the records don't match
up.
One last thing, I am trying to print this out in a
report. So, I don't know how to get this code to run in a
report; maybe it has to run in the form and then a button
to push on the form that will print it out in a report
format. I will put the code that I have typed up in here
just so that all of you can see that I am trying but,
please don't make fun of me because if this was easy
everyone would be doing it. I have bought numerous books
and even took an Access class at the local community
college and still couldn't get any help. This may be a
simple task, it may be a difficult task, I don't know,
but one thing is for sure- I would be extremely grateful
for any help. The code I wrote repeats the If for all
times I need (26 in all).
Private Sub Form_Load() 'I want the code to run when the
form opens
On Error GoTo ErrorPreview 'I want to get a debug message
if I typed code wrong
' This will determine the Time if true it will then
determine vehicle size
If Forms![ParkingLayout]![Time] = 600 Then
' This determines vehicle size for the specified Time
value
If Forms![ParkingLayout]![VehicleSize] = 35 Then
For Each Forms![VehicleNumber] In [GridId]
Dim vehicle(25)
vehicle(0) = [VehicleNumber]
vehicle(1) = [VehicleNumber]
vehicle(2) = [VehicleNumber]
vehicle(3) = [VehicleNumber]
vehicle(4) = [VehicleNumber]
vehicle(5) = [VehicleNumber]
End Sub
(begging).
I have two tables.
table1 is an excel link from a sheet that is a grid (14
columns x 7 rows). The columns are labeled numerically
and the rows are labeled alphabetically hence, a location
is given for each cell of the grid (i.e. 7B). Inside the
grid are numbers associated with vehicles of different
lengths. The lengths are 30, 35, and 40.
On a second sheet of the Excel workbook the grid is
arranged so that it is in 3 columns, it is this sheet
(ParkingLink) that is linked to Access.
column A is the grid location
column B is the associated vehicle number
column C is an If statement that determines the vehicle
size (based on vehicle number).
table2 is a non-linked table that is in Access. It
contains 3 columns: RouteId, VehicleSize, and Time.
I am trying to write code with a For Next Each that will
assign a vehicle to a route when the following is true:
1. the vehicle is first in the row (so when one has been
assigned it cannot be assigned again- the next vehicle in
line is used)
2. the vehicle in the grid is equal to the VehicleSize of
table2
3. the vehicle is assigned based on Time of table2
For example:
1A (grid#) = 35 (vehiclesize) = 600 (time) = 3103
(vehicle number)
1B (grid#) = 40 (vehiclesize) = 630 (time) = 5201
(vehicle number)
1C (grid#) = 35 (vehiclesize) = 600 (time) = code needs
to pick next column for a match in size/time because 1B
is assigned to 630.
This is what I did: I made 3 queries.
query3 has fields: VehicleNumber, VehicleSize
query2 has fields: GridId, VehicleNumber
query1 is a relationship from queries 1 and 2 so that
VehicleNumber has the GridId and VehicleSize.
I have a fourth query (query4) from table2 with fields:
RouteID, required VehicleSize and associated Time. There
are 26 records in this table/query and 24 records in
query1. Otherwise I would just join query1 and query4
together based on VehicleSize but the records don't match
up.
One last thing, I am trying to print this out in a
report. So, I don't know how to get this code to run in a
report; maybe it has to run in the form and then a button
to push on the form that will print it out in a report
format. I will put the code that I have typed up in here
just so that all of you can see that I am trying but,
please don't make fun of me because if this was easy
everyone would be doing it. I have bought numerous books
and even took an Access class at the local community
college and still couldn't get any help. This may be a
simple task, it may be a difficult task, I don't know,
but one thing is for sure- I would be extremely grateful
for any help. The code I wrote repeats the If for all
times I need (26 in all).
Private Sub Form_Load() 'I want the code to run when the
form opens
On Error GoTo ErrorPreview 'I want to get a debug message
if I typed code wrong
' This will determine the Time if true it will then
determine vehicle size
If Forms![ParkingLayout]![Time] = 600 Then
' This determines vehicle size for the specified Time
value
If Forms![ParkingLayout]![VehicleSize] = 35 Then
For Each Forms![VehicleNumber] In [GridId]
Dim vehicle(25)
vehicle(0) = [VehicleNumber]
vehicle(1) = [VehicleNumber]
vehicle(2) = [VehicleNumber]
vehicle(3) = [VehicleNumber]
vehicle(4) = [VehicleNumber]
vehicle(5) = [VehicleNumber]
End Sub