Query using multiple parameters

[boblarson]

I was never going to solve this was I?

You were definitely at a disadvantage.

 

[Brianwarnock]

LOL, tried to give you points for rescuing Shall but "need to spread them around", round spherical objects

Brian

 

[shall]

@Bob a question for you. If I setup a split screen using the code above is it possible to allow edits to the upper part of the screen for the information pulled? Specifically allowing changes to the checkShow & checkPend fields?

 

[boblarson]

As long as the form's underlying query is updatable you should be able to do that.

 

[shall]

Ok being such a noob at this how do I make sure of that?

 

[boblarson]

Ok being such a noob at this how do I make sure of that?

Open the form's underlying query and see if you can type a change in manually.

 

[shall]

It wont let me change the data

 

[boblarson]

It wont let me change the data

Then it isn't updatable. Post the SQL for the query and we might be able to help figure out why.

 

[shall]

Here is the SQL. I had taken your advice and cleared all of the criteria out of the query and placed it on the form.

SELECT Students.txtFName, Students.txtLName, Students.School.Value, Students.TxtStudentNum, Students.Show, Students.ApptDate, Students.Program.Value, Students.Active, Students.FS, Students.Rep.Value, Students.Reschedule, Students.ReDate, Students.Pending
FROM Students;

 

[boblarson]

Here is the SQL. I had taken your advice and cleared all of the criteria out of the query and placed it on the form.

SELECT Students.txtFName, Students.txtLName, Students.School.Value, Students.TxtStudentNum, Students.Show, Students.ApptDate, Students.Program.Value, Students.Active, Students.FS, Students.Rep.Value, Students.Reschedule, Students.ReDate, Students.Pending
FROM Students;

Looks like it should be updatable to me. I think you might have had less troubles had you not used the multivalue fields and instead used a junction table for each of the multivalue fields.

 

[shall]

Message it gives me is that this Recordset is not updateable.

 

[boblarson]

It is the multivalue fields which are causing the problem. Once they are removed, it works fine.

 

[shall]

It is the multivalue fields which are causing the problem. Once they are removed, it works fine.

So I would need to redo the rep and school fields are is there an adjustment I can make to them to fix the fields?

 

[boblarson]

So I would need to redo the rep and school fields are is there an adjustment I can make to them to fix the fields?

You would need to redo the rep, school and program fields.

What you should have for them if multiples can be assigned to a person then you need to use junction tables with lookup tables:

[Lookup tables]
tblReps
RepID - Autonumber (PK)
FirstName
LastName
' any other info specific to a rep

tblSchools
SchoolID - Autonumber(PK)
SchoolName


tblPrograms
ProgramID - Autonumber(PK)
ProgramName

and then you would use a junction table for each

PersonsReps
PersonID---| composite key (or you can use an autonumber and a multifield index set to no duplicates on the two fields
RepID -----|
(both are Long Integers)

PersonSchools
PersonID - Long Integer
SchoolID - Long Integer

PersonsPrograms
PersonID - Long Integer
ProgramID - Long Integer