Control Panel Form

[Bonifaceg]

Hi all.
I think this question belongs here, but I'm not sure, so bear with me.

I have a database I created to track stays, occurrences, suspensions, bars, & personal Information at a men's residence. Within this, I have a form, unlinked, (frmcontrolpanel) that I want to use as a control panel to enter & manipulate data in several tables; (PersonalInfo), (CheckIn), (SuspendInfo), (Choreinfo), (Occurrencelog), (BarInfo), & (SupNotes).

I want frmcontrolpanel to GET data from tables BunkNumber and Checkin, and to INPUT data into tables PersonalInfo, SuspendInfo, ChoreInfo, BarInfo, OccurrenceLog, and SupNotes.

How would I go about doing this? Any & all help is greatly appreciated.

The db has 2 modules in it that the form (frmcontrolpanel) uses.
I think my relationships and tables are in order, but if they arent, then please do tell.

Thanks Again,
Bonie.

 

[Dwight]

I have not looked at your database but from your description it does not sound like it is designed properly if you are trying to move data back and forth between its tables.

If it is normalized this shouldn't be necessary. The data would be in the correct table and then queries can be used to pull it together for forms and/or reports.

 

[Bonifaceg]

Thanks for the suggestion.
I'm actually not trying to move data between tables. The form in question displays the current record on mousemove, and the user then has an option to perform one or more of several actions.

The help I wanted was in with linking table Checkin to form frmcontrolpanel. Thx again, Bonie.

 

[IfButOnly]

Bonie,
I have had a look at your database but still unsure what you are wanting to do. frmControlPanel is currently bound to tblSiteBookings and I don't know why you want to bind to CheckIn.

Could you be a bit more specific on what you want to happen?

thanks .. Peter

 

[Bonifaceg]

Intentions

Hi Peter & thanks for taking the time.

The DB is meant to enter & track client stays & activity at a shelter/residence. I combine a client name with a Bunk number into the table CheckIn (ref relationships).

The form frmcontrolpanel is, as you said, currently bound to tblSitebookings. I have (will have) all my data in table CheckIn, & this is why I wanted the form to be tied to it.

What I require the form to do is to display which bunks are occupied & which are not. From there the idea is to modify the form frmBunkdetails so that One can perform different actions (Bar, Suspend, Checkout, Record Chores, Note record) pertaining to that particular client in that bunk.

Thganks again,
Bonie.

 

[IfButOnly]

Bonie,

To bind to the CheckIn table all you need to do is change the Forms RecordSource field to the new table and check any bound fields to ensure that they match to the new table.

However, I feel I am being a bit thick here and still not sure what you are trying to do... Mainly because I think your application is doing all the things that you have stated as the reason for the change.

The main problem i could see was that you are not passing the ClientID or StayID across to your Bar, Suspend, Chore ... forms and the client had be be selected when you called these forms... Is this the issue?

Peter

 

[Bonifaceg]

Clarification

Hi Peter.
You have it right on the money!
That, and the fact that I want to get the Clients names from the clients table. (I have a modified form frmBunkNumber, which has a dropdown of the clients, but I am still stuck as to how to get the FirstName, MiddleName, SSN & LastName to fill the appropriate fields; The main reason I can see is that the form frmBunkDetails is bound to the table tblBunknumbers, while the data has to come from PersonalInfo. Is there a way around this?)

I was also looking at the IIF function to open up the CheckIn form if the bunk label in frmcontrolpanel is unoccupied or Open the form frmBunkdetails if it is occupied. Is this advisable? Any other advice is very welcome......this is my first DB, & I'm still extremely green at it.

I think once that is done & I can test it with a little data, I'll then be able to start on the security & splitting the database.

Thanks again for your time, & no, you are definetely not being thick...I should have explained myself better.
Regards,
Bonie.
PS: Ive had to strip the DB of alot of things to zip it, but I think the general structure still remains.
Cheers, Bonie.

 

[IfButOnly]

Hi Bonie

Base the frmBunkNumber on a query that will put all the required info together.

In regard to opening either CheckIn form or frmBunkNumber - why are there two steps here. Couple of questions to help me visualise -

... I assume you have StayID and ClientID as a Client may return for multiple stays - Correct?
... Are Client details known and entered before Client arrives for check in- Yes, No, Not Always?
... Information should only be stored once - is client name etc in tblBunkNumber there for prebookings before you know client details?
... In tblBunkNumber you store BunkNumber as 'Bunk nn' - I doubt this is good practice - I think you should be just storig the BunkID and build the text format. I am not sure about the whole bunk number thing - is BunkID 10 always BunkNumber 10 and so forth, ie is there always a direct correlation?
... Is the Nightly CheckIn Tab (no code attached to it) the main means of check-in? If so, why not combine it with Bunk Assignment?

With these few things we should be able to make sure your DB design is okay and finish up pretty quickly.

I had hoped to ask you these questions last night and test things today but I currently am having a problem with Access - hope to fix it today and see how we go.

peter

 

[Bonifaceg]

Hi Peter.

Hope this clarifies things some.

... I assume you have StayID and ClientID as a Client may return for multiple stays - Correct?
Yes. The clients may & do return many times a year. They are, however limited to 90 days in a calendar year.

... Are Client details known and entered before Client arrives for check in- Yes, No, Not Always?
The Client Details are not always known & entered when the client comes in. In many cases (for the returnees) they are, but not always. To this end, the only required fields in table PersonalInfo are the SSN, and the names.

... Information should only be stored once - is client name etc in tblBunkNumber there for prebookings before you know client details?
I am trying to amend tblBunkNumber so that the client information is tied to table PersonalInfo. I had it thus because of frmBunkNumber (I had the names & SSN on it for quick entry of a client who has no prior record).

... In tblBunkNumber you store BunkNumber as 'Bunk nn' - I doubt this is good practice - I think you should be just storig the BunkID and build the text format. I am not sure about the whole bunk number thing - is BunkID 10 always BunkNumber 10 and so forth, ie is there always a direct correlation?
Yes, there is a direct correlation between BunkID 01 and Bunk 01. I have Table BunkNumber which I want to use as the record source for tblBunkNumber; I will then be able to use the BunkID.

... Is the Nightly CheckIn Tab (no code attached to it) the main means of check-in? If so, why not combine it with Bunk Assignment?
Nightly CheckIn separate from The regular CheckIn, and is one thing I have not been able to figure-out how to do. Here's the situation; While a client is a resident at the facility, they have to report to the desk every evening between 7:00 and 8:00 (M-F), or 6:00-7:00 (S-S). Failure to do so results in Bunk loss, and a subsequent suspension. (Correct me if I'm wrong but I've been thinking along the lines of having a temporary table created each morning with the last night's residents; Have a form for Nightly CheckIn that would delete the clientID from the table (or flag it); compare the 2 tables after the checkIn period and Check out anyone still in the temporary table, or whose record is not flagged (Have not checked-in).)

Sorry for being so verbose, & Thanks again.
Regards,
Bonie.

 

[IfButOnly]

Thanks Bonie, I will have a play with it and get back to you tomorrow.

Peter

 

[IfButOnly]

Hi Bonie, haven't had a chance to do much on it but have attached where I am to date.

I was having a memory problem yesterday (computer, mine is not an intermittent problem) which I thought was either a virus or access corruption. It turned out to be a problem with your DB relationships which wouldn't allow me to do anything, even create new db and import. Anyway, fixed now but wasted a fair bit of time. As well as that I have just become a brand new grandfather which has also caused me to be a bit distracted.

I have commented out or removed a bit of your code when I was trying to fix the memory problem and replaced it with my stuff - Don't know if one way is better than the other, but as I hadn't used some of your techniques I thought I would go with what I knew.

Have a look through frmControlPanel and frmBunkNumber to see what I have done there. I have got rid of tblBunkNumbers and use primarily tblSiteDetails and PersonalInfo.

I haven't thought through the Nightly CheckIn yet, but initial thought was that clicking "Nightly Check In" tab would change the background color of assigned beds to a different color (eg green). When you click on a bunk in frmControlPanel - if the bg color is green then display a check box (Checked In?). Clicking the checkbox changes the background color and updates a field in tblSiteDetails called dteLastCheckIn. This will allow you to see at a glance who has not check in and run reports based on dteLastCheckIn. Will keep thinking about it though.

With your business functions like Suspend Client, I have not really used macros before so am not sure how to pass arguments to them. In code though, I have stored ClientID (Might be better to be StayID) in the tag field of lblBunknn. I would look at using DoCmd.OpenForm "SuspendClient" ... with a Where condition like "ClientID = " & ClientID. Alternatively pass ClientID as the openArg in the OpenForm command.

I know I had a bit more to say (believe it or not) but my mind is on my girls.

I have been enjoying working on this Bonie, so please feel free to come back to me if you want further help. Come through the forum or email me directly on peter_rallings@dodo.com.au

Peter

 

[IfButOnly]

Attachment to large. I can either email to you directly or trim down - let me know what you want.

Peter

 

[Bonifaceg]

Thanks for the time Peter, & Congrats on becoming a Grandfather!

I also had some probs with the form frmcontrolPanel, but not as drastic as those you did (It was not showing the client's name on MouseMove).

I am looking foward to having a look at the db structure & forms.
Congrats again,
Bonie.