Update or Insert

[Swillsy]

Hi All,
I'm trying to create a 'Temporary' table in Access, I have created it and inputted a set amount of 'RoomIDs'(1-100). I need to enter data into the this table when I load up a form from another called Schedule Details.

the reason the table is temporary is so that I can generate the table depending on variable dates the user enters.

Now how would i go about entering the data into the table and match the RoomIDs so that the Temporary Table's RoomID field has 1-100(This is important as the form needs this layout to display the data):

Tables

(Schedule_details) - RoomID,StartDate,EndDate,StudentID
(TemporaryTable) - RoomID,StartDate,Endate,StudentID

Current Progress

Insert Into Temporarytable (StartDate,EndDate,StudentID)
Select startDate,EndDate,StudentID
From Schedule_Details
WHERE (([Schedule Details].EndDate)>=[Enter Start Date]) AND (([Schedule Details].StartDate)<=[Enter End Date]);

 

[Swillsy]

Hi folks,
just thought I'd reask the question as it may not have been clear:
i have a table

1 a no
2 a
3 a no
4 a no
...

The numbers will always be the same i.e upto 10
I need the blank spaces so I can display them as blank on my form.
How would I go about updating this table from another table
e.g updating 2 to say 'yes' or make 3 display blank.

 

[NigelShaw]

Hi Swillsy,

( you're local! )

you question isnt that clear but-

if you open your temporary table as a recordset so you can enter the data without a form and decide your condition to change your temporary data.

you can set a variable that sets data in the recordset as a value on your form or look at default value.

sometimes i have a mainform and when i call another form, have the default value of the other form set to a specific value on my mainform so the data is always correct.

Regs,

Nigel

 

[Swillsy]

Hi Nigel its not exactly sunny today!
Well its a bit of an odd problem tbh I needed a temporary table to base a booking plan on -looks pretty cool now

I have decided to sort this out using an Update query which "Nulls" all the records not required and then I follow this with an Update query which loads data onto the temporary table IF it falls between two dates.
Works like a charm now and I have put them before the loading of the form and turned warnings off etc. Heres the code I used -

UPDATE AvailDisplay
INNER JOIN [Schedule details]
ON AvailDisplay.DisplayRoomID=[Schedule Details].RoomID
SET AvailDisplay.DisplayStartDate = [Schedule Details].RoomID,
etc etc
WHERE (([Schedule Details].EndDate)>=[Enter Start Date]) AND (([Schedule Details].StartDate)<=[Enter End Date]));

Now I just need to get an input form inserting the same data onto two tables - after I've had soom food