UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Simple Db But Stuck On An Input Form, Access 2013    
 
   
crookedacre
post Feb 14 2018, 04:51 PM
Post#1



Posts: 24
Joined: 11-December 12



Hello!
I have a simple DB just 3 tables designed to track changes in a state. The PDF shows the table structure and relationships. Basically a state table, a table containing the change effective date, and ta table of the change(s) being made. I'm hoping for help in creating a form that selects the state needed and then displays the existing dates & changes in a datasheet view like the form on the PDF uploaded. That form is a form with subforms based on the tables. I couldn't get it to build based on a query. I thought a parameter query to get the state then display the data on the form but can't figure out how. Because the tables relationships if the state has no entry (a change) then it shows blank, not even the state abbreviation. the state table is pre-populated with all states. Can someone provide some direction to me please? I'm a beginner of course smile.gif
Thank you!!

Attached File(s)
Attached File  Document1.pdf ( 143.99K )Number of downloads: 3
 
Go to the top of the page
 
doctor9
post Feb 14 2018, 05:01 PM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


crookedacre,

Based on your description, I'd move the "effective date" field into the third table that lists the changes, and remove the middle table that only holds dates.

Like this:

tblStates
StateID [Autonumber, Primary Key]
strStateName

tblChanges
ChangeID [Autonumber, Primary Key]
lngStateID [Foreign Key to tblStates.StateID]
dteEffectiveDate
strChangeDescription

Then, when you want a query to list every state (even the ones with no changes), use a Left Join between the state table and the changes table instead of an Inner Join. If you use the Design Grid view for the query, you can edit the relationship and select the one where it shows all records from the States table and only those records from the changes table where the joined fields are equal.

For data entry, use a Single Main form/Continuous subform setup; the main form can be based on the States table, and the subform on the table of changes, sorted by effective date.

Hope this helps,

Dennis
Go to the top of the page
 
crookedacre
post Feb 14 2018, 06:14 PM
Post#3



Posts: 24
Joined: 11-December 12



Thank you I will try that!
Go to the top of the page
 
crookedacre
post Feb 15 2018, 10:49 AM
Post#4



Posts: 24
Joined: 11-December 12



I reduced to 2 tables as suggested and modified the query in design view "SELECT State.ST_AB, Change.EffDate, Change.Code, Change.DescCode, Change.Description, Change.lkType, Change.SentNx, Change.CreatedDt, Change.ST_ID
FROM State LEFT JOIN Change ON State.[ST_ID] = Change.[ST_ID]
WHERE (((State.ST_AB)=[State])); and the query works fine.

If I make a form with an unbound control (ComboBox) to select the state, and modify the query so that the WHERE states WHERE (((State.ST_AB)=[Forms]![Form1]![Combo0]));
The results are blank. No state code, nothing. I added an OK button to the combo form with DoCmd.OpenQuery "QryGetStateData", acViewNormal, acEdit
All I get is an empty grid with the field names. Where Did I go wrong!
Go to the top of the page
 
doctor9
post Feb 15 2018, 11:10 AM
Post#5


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


crookedacre,

If you want to use a combobox to select the state, you can set up the form this way:

1. Set the RecordSource to be the tblChanges table. No query. No Where clause.
2. In the form's Open event, run this VBA:

CODE
Me.FilterOn = True
Me.Filter = "lngStateID = 0"


This will cause the form to show no records when the form opens.

3. In the combobox's AfterUpdate event, run this VBA:
CODE
Me.Filter = "lngStateID = " & Me.NameOfComboboxHere


Use your own combobox name of course.

The combobox should have two columns in it's rowsource: StateID and strStateName. Column Count = 2, Column Widths: 0";1"

This will basically allow the user to select a state. After they do, the form will filter to show only records where the foreign key (lngStateID) is equal to the state selected in the combobox.

Hope this helps,

Dennis
Go to the top of the page
 
crookedacre
post Feb 15 2018, 11:43 AM
Post#6



Posts: 24
Joined: 11-December 12



I'm sorry I should have been more clear.

I created a "Pick a State" form with an unbound control combo box that is getting the data from the State table. The data from Row Source Type is Table/Query - and Row Source is "SELECT [State].[ST_ID], [State].[ST_AB] FROM [State] ORDER BY [ST_AB];" which Access did for me when I added the Combo box to the form

I then built a query that a results form and subform is based upon. The criteria on the ST_AB field in that query is (((State.ST_AB)=[Forms]![Form1]![Combo0]));
That returns nothing.

However if I use [ Entrer State] as the criteria on the query, and I open the results form the prompt appears Enter State, I type in CT and the query returns the results on the form/Subform just fine. It's when I try to get the state code from the combo box and pass to the query something is not right.

the query to populate the results form does not appear to be getting the criteria from the combo box on the "Pick a State" form
Go to the top of the page
 
doctor9
post Feb 15 2018, 01:10 PM
Post#7


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


crookedacre,

I would recommend that you NOT use the query to filter your data. Instead, just use the form's .Filter property, which is much simpler. Your form's query should display ALL records by default. Filter that data based on the combobox's value by setting the form's .Filter property rather than changing the query.

Hope this helps,

Dennis
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2018 - 03:45 AM