List Box to Populate Form (1 Viewer)

[Sweetnuff38]

Hello

I have a form (frmDispatched), and a query (qrySchedules).

I would like to have a listbox on the frmDispatched that has the schedules information in it that would allow the user to click on any given schedule and have that information populate into the frmdispatched(table).

From there the user would continue and data enter all other information required for the dispatch.

Dont think I am googling the question correctly as I am not having the best of luck finding a solution to this and I am sure it isnt that difficult.

Any help would be very much appreciate.

Tracy

 

[GinaWhipp]

I *think* what you are looking for is an APPEND query. So when a User click on a selection in the List Box the APPEND query would run and send it to your desired table which does not sound like it is part of your RecordSource.

 

[Sweetnuff38]

Thanks Ginna

I made the qrySchedule an append query and it does send the data to the tbldispatched which is part of what I wanted to accomplish.

So now how do I make it so that when I click on a record in the listbox it shows all fields show on the form?

 

[GinaWhipp]

Run a...

Me.YourListBoxName.Requery

...right after the APPEND query.

 

[Sweetnuff38]

I need to explain this better as it has become more complicated than originally thought, I am not an expert in access so any help would be appreciated in how to get this to work:

I have a form named frmDispatch; I am trying to figure out how to do the following when I enter the OperationDate:

Have access look in the tblSchedules to determine the following:

Does the OperationDate fall between the EffectiveFrom and EffectiveTo dates (Equal to or greater, less than etc)

Look at the DaysofOperation field in the tblSchedules and determine what days of the week by number of the day of the week does the trip run ie: 123----, ---4567. Once the number of day is determined find the trip number from the tripnumber field in the tblSchedules that applies to all the above conditions.

Then have a list box on the frmDispatch form that when all the above conditions are met appends only the relevant records from the tblSchedule to the tblDispatch and shows in the said list box.

The following fields would be appended:

TripNumber
Departure
DepartureTime
Arrival
ArrivalTime

The user would then be able to click on the relevant record and it would populate into the frmDispatch where the user would complete the trip information.

Once all information is entered, have a way to close the record and remove it from the listbox.

I would appreciate any help anyone is able to give! Thanks

 

[GinaWhipp]

Sounds like an UPDATE query, maybe more then one... Not sure how to help because, you are right, it is a complicated database if you don't do this daily. Do you have a sample database you could upload for review?

 

[Sweetnuff38]

Im not able to share the database. Should I be reposting this somewhere for more responses?

 

[GinaWhipp]

No, others will pass by... Well, I should say you can if you wish but others will pass by. And note, I do not want the data, if that is your concern just the structure.

As I said sounds like an UPDATE query should do the trick, have you tried that?

 

[Sweetnuff38]

Gina,

Sorry I am just getting back to this. I am trying the update query.

Can you or someone point me in the right direction on how to write the following?

If OperationsDate in tblDispatch is greater than or equal to EffectiveFrom and less than or equal to EffectiveTo in tblSchedule AND

If WeekDayNumber in qryDispatch (eg. 1) is found in DaysofOperation tblSchedule (ie 1234---)

Then run update query to return all applicable records.

Make sense?

Tracy

 

[GinaWhipp]

You can run an update query to the tables included in the one UPDATE query. It doesn't sound like they are all in the same query...