help with record set

[mambo21]

Hello,
can someone help me with this code, I am having trouble making recordsets for it

Private Sub Command0_Click()

volstring = Null 'String to hold the volunteers that have been processed

DoCmd.SetWarnings False
'Cretae a loop, we'll do our own exit condition later
Do While 1 = 1

'If the volString is not empty, we check for volunteers NOT processed
If IsNull(volstring) = False Then
getVol = DLookup("Volunt_Id", "volunteer", "Volunt_Id NOT IN (" & volstring & ")")
'If the volString is empty, we check for the first volunteer
Else
getVol = DLookup("Volunt_Id", "volunteer")
End If

'If we cant find any more we exit the loop
If IsNull(getVol) = True Then
Exit Do
Else

'Lets get their first priority
check1 = DLookup("First_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

'If it is not taken
If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")

Else
'we check for thier second one
check1 = DLookup("Second_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")
Else
'we check for their third one
check1 = DLookup("Third_Choice", "tblChoice", "Volunt_Id = '" & getVol & "'")
'Now we check if it has been taken, or if their is no priority, assume it is taken
If IsNull(check1) = False Then
checktaken = DLookup("assignedto", "tblProject", "Project_Id = '" & check1 & "'")
Else
checktaken = Null
End If

If IsNull(checktaken) = True Then
'Add it to the table
DoCmd.RunSQL ("UPDATE volunteer SET assignedto = '" & getVol & "' WHERE Project_Id = '" & check1 & "'")
Else
'NOTE: This is your code for what to do if ALL their priorities have been taken
End If

'Now we add the volunteer to the list we dont process
volstring = volstring & "'" & getVol & "',"
Loop
DoCmd.SetWarnings True

End Sub

Cheers

 

[Shadez]

What are you trying to do?

 

[mambo21]

The code is for allocating students according to their project choices. As if first choice is taken the volunteer is allocated to second choice and if second choice is taken they are allocated to third choice.

That is the code I wrote and I will attach the database that goes with it.

cheers

 

[Shadez]

ok now what is the problem, what errors are you getting how can we help?


[edit] temporarily removeing the error handleing would help deguging.

 

[mambo21]

On the form when I click the button it should do the allocation but it bring the error saying that User_defined type not defined

I thought the error was due to recordsets, I think the code is right and should work but I can not get my finger around it

Any help would be appreciated

cheers

 

[Mile-O]

The User Defined Type error is probably due to using DAO in an ADO specified database.

However, just looking at the code you've initially posted I can see three things wrong with it immediately.

1. You turn off warnings for the whole procedure so any error message you may get does not show;
2. There is not enough End If statements to meet the number of If statements opened
3. As your table seems to have three different choices this is an indication that your structure is incorrect (does not meet First Normal Form) and is more than likely causing some of your problems

 

[Shadez]

On the form when I click the button it should do the allocation but it bring the error saying that User_defined type not defined

I thought the error was due to recordsets, I think the code is right and should work but I can not get my finger around it

Any help would be appreciated

cheers

ok now you know what the error is you need to know where its occuring?

Open the vba editor then run the code when the error message apears press debug, the problem line of code should be highlighted in yellow. If this doesnt work use a break point at the start of the function and step through the code a line at a time (f8).

note "User_defined type not defined" means that one or more of your variables have not been defined properly. i suggest you fully define ALL the variables you are using. To help this type "Option Explicit" at the top of the module.

 

[mambo21]

Hey, I have decided to change the code because i thought that was the long method of doing it.

Here is what I need to do.

1.Run the qryImport1 to import all the first choices to tblTemp_choices(Volunt_Id, Project_Id)
2.Take Volunt_Id and Project_Id from the first volunteer
3. Check if the project is allocated from tblProject.Allocated
4.If allocated column in tblProject is Yes then delete the student from tblTemp_Choice
5.If Allocated column in tblProject is No go to tblCoordinator to check if Remain_Places is 0
6.If Remain_Places is 0 then delete the student from tblTemp_Choice
7.If Remain_Places is not 0 decrement the value on the column by 1
8. Then go back to tblProject and change Allocated column to Yes
9. The repeate steps 2 to 8 for the second volunteer untill the last volunteer
10. Match the Volunt_Id from tblTemp_Choices with Volunt_Id in tblChoice then change Allocated tblChoice to Yes
11.Insert the results from tblTemp_Choices into tblAllocated_Projects
12. Clear tblTemp_Choice
13. Repeate processes 1 by running qryImport2 upto process 12.
14. Repeate processes 1 by running qryImport3 upto process 12.

All this should be done by a click of a button on frmAllocation behind the scenes.

That is about it really, Any ideas on how i can do this? I have attached the database again because the old database needed some changes

Thanks in advance

 

[modest]

As your table seems to have three different choices this is an indication that your structure is incorrect (does not meet First Normal Form)

I do not think it is possible to have an Access/Excel table in anything less than 1NF - this has to do with table structure, which is defaultedly in 1NF. In anything less, you would have to be referencing actual memory slots and have a table who's records don't all share the same columns.

1NF is when a field value is indivisible.