Access 2003 Project

[ethan.geerdes]

Good Morning everyone,
I had a question about making a project. I want to create a project that allows users to look up information about the systems we own. I created a table for the Systems and a table for advisory messages and security updates. I have created forms to submit the data into the tables but I need to find a way to get all that information back based on the system. I have been reading up on relationships between tables and queries and stuff but am still not sure exactly what I would need to do to be able to have the end users review the data that was inputted. Any ideas on how I should go about this?

 

[Ranman256]

I would start with a search form. The form would allow user to input a keyword.
add a Find button, this will open a Results form (based on a query based on the text in the box.)

The Results form would be a continuous form showing all the records in the query.

A 3rd form would open when the user selects the 1 record in the list, clicks a DETAIL button at the top to open a single record form, that shows ALL fields of the single record.

This is how I do it.

 

[ethan.geerdes]

A 3rd form would open when the user selects the 1 record in the list, clicks a DETAIL button at the top to open a single record form, that shows ALL fields of the single record.

This is how I do it.

How would I go about option three? would you still have that results form? what if I made a Form and had 3 subforms in it to pull up the information I needed. What kind of relationships would I need to establish for this? Thanks for your help.

 

[Ranman256]

if you start adding multiple sub forms , you will run out of memory. (did it)
you could have 1 sub form, and tabs. When the user selects, phones tab, you switch the
recordsource to the subform... me.sub!forms.recordsouce = qsPhones.
then user clicks History tab
set the subform to THAT query.

but if you have continuous form, then user clicks a DETAIL button in the header, then just open the detail form via
docmd.openForm "frmDetail",,"[clientID]= "& txtClientID

either way works.

 

[ethan.geerdes]

Ok, so let's say that I'm working on tracking updates and advisory messages for applications that run on some systems. I got a table for the system name and I have created three tables for the security updates, the advisory messages and the patches themselves for each system. I have created three forms based off the tables to allow the user to input data a little easier. If I went with the subform approach, you said it would eat up my memory. If I was to recall the information for a continuous form, what kind of relationships would I need to create? Do I need to create any relationships? How would I go about the actual recall of information?

 

[JLCantara]

A very similar issue was posted lately. Here is my suggestion:

- Create a form with 2 tabs: page 1 for Advisory and page 2 for security.
- Clean up the MS garbage in the header and create in it a combo for the systems
- Create a query on Advisory table, sorted by System (whatch out this is a reserved word).
- Create a continuous form based on this query.
- Add it to page 1: link SystemID of the subform to the header combo.
- Create a query on Security table, sorted by SystemID.
- Create a continuous form based on this query.
- Add it to page 2: link SystemID of the subform to the header combo.
- When user selects a system, both pages will be auto. updated.

Good day, JLCantara.

 

[ethan.geerdes]

So I created the subform based off the query but I can't seem to get the records to pull up from the table. Basically, I created a form called Systems with a combobox called "System Name" which is pulling it's data from the System Name column on the Systems table. I created a button that is to pull up the advisory messages sub form. The subform is based off of a query for the "advisory messages" table which contains every field of the Advisory Messages table.

How do I make it so that based on the selection combo box of the parent form, it pulls up the records for that system in the sub form?

 

[JLCantara]

How can we answer your question? We have no idea about your DB structure, queries and forms... Please make available a version of your app.

 

[ethan.geerdes]

Hey, thank you for your reply I figured it out right after I posted it. Thank you.

 

[ethan.geerdes]

Is there a way to transfer records between tables based on a true/false concept? Let's say a user is entering records, they have a combo box for yes/no. If yes=true then move record to THIS table. IF no=False, then move the record to THAT table. The issue I have though before trying to see if I can do it, is what if the value for THAT table becomes true? Is there a way that I would then be able to move it to THIS table without screwing up my records or having duplicates? How can I control this movement?

 

[ethan.geerdes]

I am also having trouble setting the focus on my forms... I have a parent form with two labels that are coded like this.

Private Sub Advisory Messages_Click()
Me![ Advisory Messages].Visible = _
Not Me![Advisory Messages].Visible



End Sub

Private Sub Security Updates_Click()
Me![Security Updates].Visible = _
Not Me![Security Updates].Visible

End Sub

I have the visible property set to no on the subform allowing the user to toggle the visibility when the label is clicked. The problem though is when I click inside the subform to use the scroll bar to view records, it transfers the focus to the subform making it almost impossible to close by clicking the label again because the label is on the parent form. I found the "me.parent.setfocus" command and a few other set focus commands but I don't know what I should be applying the command to in order to make it work. Any help would be appreciated.