need guidance please in a form...

[rsbutterfly16 via AccessMonster.com]

hello can anyone please help me with this form?


I have a form in which has one drop down combo box and one data entry text
box.

The combo drop down box comes from a query of all active contracts from my
contracts table.
the second box has a unit ID number which the user enters.

All information entered in this data entry form goes to a Unit table.

My contract table has three columns: Name of contract and Type (only three
types): A, B, C. (contract table) and then a column that says active or
inactive.
i.e

Name Type Status
113 A active
114 B inactive
115 A active
116 A inactive
120 C active
121 C inactive


This is where I am stuck…

When a user puts an unit id number and then selects from the listbox one of
the active contracts for the first time is ok. For example: the user entered
unit number 123 and selected contract: 113 which belongs to type A.
What I need is that next time the user put the same unit number in the text
box then the contracts he could choose for that selected unit number could
only any of type B or type C. he/she can no longer choose other type A.

Can someone give me some light in how to start this?

 

[rsbutterfly16 via AccessMonster.com]

hello can anyone please help me with this form?

I have a form in which has one drop down combo box and one data entry text
box.

The combo drop down box comes from a query of all active contracts from my
contracts table.
the second box has a unit ID number which the user enters.

All information entered in this data entry form goes to a Unit table.

My contract table has three columns: Name of contract and Type (only three
types): A, B, C. (contract table) and then a column that says active or
inactive.
i.e

Name Type Status
113 A active
114 B inactive
115 A active
116 A inactive
120 C active
121 C inactive

This is where I am stuck…

When a user puts an unit id number and then selects from the listbox one of
the active contracts for the first time is ok. For example: the user entered
unit number 123 and selected contract: 113 which belongs to type A.
What I need is that next time the user put the same unit number in the text
box then the contracts he could choose for that selected unit number could
only any of type B or type C. he/she can no longer choose other type A.

so basically for every unit id number i could have only one contract type A,
only one contract B and only one contract C.

 

[Edward Reid]

butterfly,

Could you tell us a little more about your application? In reading your
description, I get a sense that this is something better addressed from
a slightly different point of view, but I couldn't figure out how the
real-world parts fit together.

Edward

 

[rsbutterfly16 via AccessMonster.com]

thank you Edward, basically each unit number can have one contract from each
type, and i only have type A, B, or C.

If the user already selected in the form that the unit number has contract
2365-6565 which belongs to type A , then next time if he wants to choose 2365-
9653 which also belongs to type A, it shouln't let him, just to choose type B
or C. hope this makes sense to you...

 

[Edward Reid]

If the user already selected in the form that the unit number has contract
2365-6565 which belongs to type A , then next time if he wants to choose 2365-
9653 which also belongs to type A, it shouln't let him, just to choose type B
or C. hope this makes sense to you...

Perhaps the problem is that I don't know what "unit" refers to. For
most problems presented here, it's difficult to come up with a good
answer without knowing something about the background.

Based on the above, the obvious response would be "why not just have a
form for the unit, with three blanks (or menus) for the contract
numbers for type A, B, and C". Probably there's a good reason that's
inconvenient or impossible in your application. But without knowing why
it can't done that way, it's hard to say what a good way would be.

Edward

 

[rsbutterfly16 via AccessMonster.com]

Thanks Edward , unit number referers to a unit number for a hospital.
the user will type the number of the hospital so basically i need to form
first to search that unit number in the destination table (Destination) for
the unit number if it exists then look for the contract number picked from my
cbocontract (coming from an active contract query) and to see the type of
contract that its is and to display only the other types in the cbo contract.


this is what i have so far, but i am really breaking my head; if you can
please give me some pointer i would extremely apreciate it...

Private Sub cmdNew_Record_Click()

On Error GoTo Err_cmdNew_Record_Click
[Form_Unit Data Entry Form].Unit_Number.SetFocus


If Len([Form_Unit Data Entry Form].Unit_Number.Text) > 0 Then

Dim Unit_Number As String
Dim strSQL As String


'---------Variable that gets the Unit_Number that was input.--------------
-------
Unit_Number = [Form_Unit Data Entry Form].Unit_Number.Text

'---------Looks for that Unit_Number in the Unit table--------------------
-
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb

For i = 0 To dbs.QueryDefs.Count - 1 'deletes the qryUnitateUnit_Number
if it is there
If dbs.QueryDefs(i).Name = "qryfind_Unit_Number" Then
dbs.QueryDefs.Delete ("qryfind_Unit_Number")
End If
Next

'query to find Unit_Number if it exists in Unit Table

strSQL = "SELECT Unit.Unit_Number, Unit.Unit_Number, Unit.PM_Contract_ID,
dbo_Contracts.Contract_Type, Unit.Status" & _
" FROM dbo_Contracts INNER JOIN (dbo_Unit_Names INNER JOIN Unit ON
dbo_Unit_Names.Unit_NUmber = " & _
"Unit.Unit_Number) ON dbo_Contracts.PM_Contract_ID = Unit.PM_Contract_ID
" & _
"WHERE Unit.Status = Active AND " & _
"Unit.Unit_Number" & _
"= """ + Unit_Number + """"

Set rst = dbs.OpenRecordset(strSQL)
'rst.MoveLast
If rst.RecordCount > 0 Then
'---------Unit_Number is in the table---------------------


'ADD THE NEW SQL COMMAND HERE TO PICK THE CONTRACTS THAT YOU WANT TO
SHOW

'strSQL = "SELECT dbo_Contracts.PM_Contract_ID, dbo_Contracts.
Contract_Type, dbo_Contracts.Begin_Date, dbo_Contracts.End_Date, Unit.
PM_Contract_ID , Unit.Contract_Type, Unit.Unit_Number, Unit.Unit_Number, Unit.
Status" & _
'"FROM dbo_Contracts LEFT JOIN (dbo_Unit_Names RIGHT JOIN Unit ON
dbo_Unit_Names.Facility_entify_code =Unit.Unit_Number)ON dbo_Contracts.
PM_Contract_ID = Unit.PM_Contract_ID" & _
'"WHERE (((dbo_Contracts.Begin_Date)<Now()) AND ((dbo_Contracts.End_Date)

Now())AND ((Unit.Contract_Type)<>[dbo_contracts].[contract_type]) AND ((Unit.

Status)="Active"))" & _;


Set qdf = dbs.CreateQueryDef("qryUnitateUnit_Number", strSQL)
Me.combo_contractquery.Enabled = True 'combo box contract query
becomes enabled
combo_contractquery.RowSource = strSQL 'to refresh rowsource from
new query
combo_contractquery.Requery 'refresh

Else 'creates new record
'---------Unit_Number was not found, so it will be a new record.----------
----------

strSQL = "SELECT PM_Contract_ID FROM dbo_Contracts "
Me.combo_contractquery.Recordset = strSQL 'new record set

'Add the query to the control source of Contract

Set qdf = dbs.CreateQueryDef("qryUnitateUnit_Number", strSQL)
Me.cboStatus.Enabled = True
Me.combo_contractquery.Enabled = True
Me.ComboA.Enabled = True
Me.ComboB.Enabled = True
Me.ComboC.Enabled = True
DoCmd.GoToRecord , , acNewRec 'add new Unit_Number

End If

Exit_cmdNew_Record_Click:
Exit Sub

Err_cmdNew_Record_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Record_Click

End If

End Sub

 

[Edward Reid]

this is what i have so far, but i am really breaking my head; if you can
please give me some pointer i would extremely apreciate it...

butterfly,

Sorry to be so long responding on this. I think I can see a couple of ways
of doing it. But I have to ask again -- is there a reason you can't just
put three lists on the form? One for each of the three types on contracts?
Is it because the users don't know the type of contract until they enter
the number? Some other reason? If you did it this way, you should be able
to lock the fields for the types previously recorded, if the intent is that
the user be unable to alter them.

I think it's pretty easy to set up the RowSource for the combobox so it
does as you've described, but before going any farther I'd like to whether
it's feasible to use what seems to me to be a simpler way. And to know what
progress you've made in the past week ...

Edward

 

[Practical Mystic]

I guess I'm not doing such a good job of getting at what I need, which is
darn near a step-by-step outline of what I need to do.

I'm working from an access db that someone else put together, and fighting
with their Access 97 constraints which don't have all the nice bells and
whistles of Access 2003 we have at home. I think that may change this week,
though, which will make my job somewhat easier in many areas!

The form I'm working from has 2 different checkboxes that need to be summed
so that the report will count the number of checks during a given date
parameter and tell the manager how many of the records have none, one or
both of these 2 fields checked. The report for the records includes both
detail and summary info.

I have a query which pulls the data together for the report and the only
'calculations' set there are date parameters and counting the ID to give her
a count of records in the report. I have looked back into the old db forms,
queries and reports and can't find out how the boxes were counted in a
different form, but it looks like the programmer who set it up used a macro
to do it, maybe in the query, but I can't seem to get that to work by
duplicating and changing field names.

When I tried setting an abs value for the check boxes in the Report, it gave
me an error message, so I must not have put it in the right spot (I used
Properties Control Source for the Sum of the check boxes).

So, pardon my lack of VBA or programmer experience. I appreciate all your
help.
Kathy

 

[Edward Reid]

You posted this as a followup to an entirely different thread. Changing
the subject line doesn't make it part of the original thread; only
actually doing a followup to the thread marks the message as part of
the thread. Those who were helping you in your thread probably didn't
see this posting.

Edward