add multiple records at once???

[Muzicmn969]

dont know how common this is but i am being tasked with finding a way to add multiple records at once.... for instance, my DB keeps track of military airspace that goes active and cold throughout the day. right now i have it set up where the user selects an airspace and fills in the data, date, time, estimated time going cold etc....

thier request is that they have the ability to select multiple airspaces and only enter the times and dates once for all of them.

Right now there is a table for airspace definition and another table for events (this one tracks the actual times)

example

__ W123 , date hot , time hot , date cold , time cold
__ W234 , date hot , time hot , date cold , time cold
etc...

the W### is the name of the airspace

any ideas would be greatly appreciated

Ricky

 

[samehkh]

you can simply add a boolean field to this table call it [selected] for example, and display it as a check box, along with the airspace name, in continious form with unbound text boxes date1, time1, date2 and time 2 plus a comand button place thos new controls in the header.

Then let the command button run an update query

update tableX
set datehot=[date1], timehot=[time1], datecold=[date2], timecold=[time2]
where chosen= true.

(syntax is not exact)

sounds good?

 

[KKilfoil]

You could restructure your db a bit so that your event table contains the one-time event-specific stuff (like [DateHot], etc) and create a new table to contain the recurring event information (i.e. your list of airspaces), which would be related (one-to-many) back to your EventID field (or whatever you called your event table primary key).

A datasheet subform with a combobox control to select the airspace(s) could make data entry easy. I'd insert a multifield index on the EventID and AirSpaceID to ensure that a given airspace were only selected once per event.

This avoids VB coding if that is an issue.

P.S. - I'd avoid the use of spaces in your fieldnames (e.g. Date Hot) to avoid problems later. Try Date_Hot or DateHot.

 

[Muzicmn969]

appreciate the tips

gonna try it out this weekend

will let ya know if it works

and why would spaces in the field names cause problems???

as you can tell i am not a pro at this but am learning everyday with help from you guys

thanks again

Ricky

 

[Pat Hartman]

Programming languages such as VBA do not allow the use of objtect names that contain embedded spaces, period. Many languages also do not allow special characters. Other languages use special characters for specific purposes such as implicitly defining a variable's data type. If you need to ever do anything with VBA and that includes something as simple as creating an event procedure for the click event of a button, you will be forced to surround any offending object name with square brackets. In some cases, Access takes it upon itself to replace spaces with underscores so you may be trying to write code thinking a sub has one name when in fact, Access has renamed it on you.

 

[Muzicmn969]

Very interesting....

never knew that, like they say you learn something new everyday...

just think of all those databases i wrote that might have problems in the future....hehehe

oh well

thanks for the advice

Ricky