I NEED HELP!!! Access 2013 using forms - to retrieve and enter data to & From Tables

[eagle1468]

OMG I am about to pull out all my hair!!! (what is left anyways...)



Here is an idea of what I am doing.

I have:
Table -- "Details"
Table -- "States"
Table -- "Units"

Query -- "UnitsInUse"
Query -- "OpenUnits"

Table "Details" has:
TennantID (PrimaryKey & AutoNumber)
FirstName (Short)
LastName (Short)
FullName (Calculated [FirstName & " " & [LastName])
Street Address 1 (Short)
Street Address 2 (Short)
City (Short)
State (Short)
Zip (Short)
Phone1
Phone2
Phone3
UnitID (Number)
UnitNumber (Short)
UnitInUse (Yes/No)
MoveInDate (Date/Time)
DepositCredit (Currency)
LockCredit (Currency)
RefundAmount (Currency)
RefundDate (Date/Time)
MoveOutDate (Date/Time)

Table "Units" has:
UnitID (PrimaryKey & AutoNumber)
UnitNumber (Short - No Duplicates)
UnitInUse (Yes/No)

Table "State" has:
State (PrimaryKey & Indexed - No Duplicates)
FullStateName

Query "UnitsInUse" has:
[Units]![UnitID]
[Units]![UnitNumber]
[Units]![UnitsInUse] (Criteria "True")

Query "OpenUnits" has:
[Units]![UnitID]
[Units]![UnitNumber]
[Units]![UnitsInUse] (Criteria "False")

Relationship has:

Details >> Units
UnitID UnitID

Basically:
Table "Units" is a list of all the Units we have.
Table "Details" is the records of each Tenant and information we have in their respective Unit

I am trying to create Form to:

1.Enter the Data into Details -- FirstName,LastName,StreetAddress1,StreetAddress2,C ity,State,Zip,Phone1,Phone2,Phone3,
UnitID,UnitNumber,UnitInUse,MoveInDate,DepositCred it,LockCredit,RefundAmount,RefundDate,MoveOutDate
^^^^ This I can Do Just by using a normal Form Wizard....

2. For Field "State" < Use ComboBox to pull data from Table "State" << Done & Works

3. (here is the problem) For Fields "UnitID", "UnitNumber", "UnitInUse" I need to Show and Select the "UnitNumber" (That the tenant is going to use) in a ComboBox then "CheckMark" the "UnitInUse" CheckBox (can set Default value to "True").

These actions need to do the following --
1. Put the Data ("UnitID" & "UnitNumber") of ComboBox "OpenUnits" into Table "Details" Feilds "UnitID" & UnitNumber.

2. Put the Data (CheckMark ("True")) into Table "Details" Field "UnitInUse" AND Table "Units" Field "UnitInUse".

This way the Queries will work. -- I've been trying to use the Query "UnitsInUse" to Populate the ComboBox "OpenUnits" but this Fails everytime!!! -- I can't seem to get it just right.....

Please Help me out!! I'm killing myself trying to figure out where I have went wrong! 3 days and counting -- I've even scrapped the database and started over from scratch!


Thank you in advance!!!!

[June7]

Should not save UnitNumber in Details, just UnitID. Or use UnitNumber as the primary/foreign key and get rid of UnitID.

UnitInUse field is not needed in either table. This status can be determined through queries.

[eagle1468]

Should not save UnitNumber in Details, just UnitID. Or use UnitNumber as the primary/foreign key and get rid of UnitID.

UnitInUse field is not needed in either table. This status can be determined through queries.

Ok, I see what you mean for Getting rid of UnitNumber.

But how can I get the Info I need about the UnitInUse Satatus from the Query....
I need to Show Only the Units In use in one query and Only the Units Not In use in another. But these Units may go In and Out of use. Each time with different Tenants. -- The Details Table needs to keep track of what Tenant had what Unit even if they are no longer in it....

How can that be done???

Thank you!!

[June7]

If the UnitID is in the Details table then it is in use.

Do a query that joins Units to Details, join type 'Include all records from Units ...'

If Details.UnitID field is empty, the unit is not is use.

[eagle1468]

If the UnitID is in the Details table then it is in use.

Do a query that joins Units to Details, join type 'Include all records from Units ...'

If Details.UnitID field is empty, the unit is not is use.

That will not work.
We have 278 Units and when a tenant gets a unit it will be in the "UnitID" Field in Table "Details" If the Tenant moves out of the unit (The Unit is no longer in use) the record in Table "Details" has to keep the "UnitID" from previous tenants for records. But the Unit will now be available for a new tenant.
That is why I had the "UnitInUse" CheckBox. Because that doesn't need to stay Checked when they move out. Only when they are currently in the unit.

But I cannot get the form to convey that information to the Tables.

Any thoughts?

[June7]

Okay first build a query that pulls records from Details with filter criteria: WHERE MoveOutDate Is Null

Now build query that joins Units to that query.

[eagle1468]

Got it! Thank you!!!!