Full Version: Populated information in Form Design
UtterAccess Forums > Microsoft® Access > Access Forms
We need help on the form and table designs. We have two tables that consists of the following fields
embers Table (tbl_Members):
MemberNumber (Numeric)
MemberType (either “Full Member” or “Social Member”)
Location Table: (tbl_Location)
LocationID (Numeric – such as 1, 2, 3, 4, 5 ..)
LocationDescription (such as “Computer Room”;”Library”; “weight room” etc.)
Oencounter two main difficulties:
1. Let’s say that the requirements are: We will not accept more than 500 full members and 100 associate members. The Social Member can visit some locations within the college.
I created a form to see the total headcounts between these two types. It was done.
On the data entry, when the form opens, I would like to have this logic inserted: (I am unsure whether this code will be working.)
Private sub CmdNew_Click()
If DCount("[MemberType]", "tbl_members", "[MemberType]= 'Full Member'") > 500 Or DCount("[MemberType]", "tbl_members", "[MemberType]= 'Social Member'") > 100 Then
MsgBox "You can't add more than 500 Full Members or more than 100 Social Members", vbRetryCancel
DoCmd.OpenForm (frm_addMembers),
End If
End Sub
My question is -> what section should I put this code into? Is it “Before Update” or “After Update” or “On Open” or “On Load” ….? I would like to hear your suggestions.
2. Difficulty:

We would like to set the rule that, if the locationID is greater than 4, the Associate member cannot precede the transaction to use the amenity, while there are no restrictions to the Full Members.
What we want to do on the form are:
When we enter the MemberID, at a specific location (ID), the system will validate the eligibility.
I created a validation form with an unbound field, called “LocationNumber” that will allow the user to select the number of the LocationID field from the Location table. I also created another unbound field, called “MemberNo” to check or lookup to the MemberNumber field from the Members table. User can select from the drop down Combo box.
When the MemberNo has been selected, I wish to have the MemberType automatically populated and matched the MemberNo record, whether it would be “Full Member” or “Social Member”. I created another unbound field called “TypeMember” to have this information populated. However, I was unsuccessful. It has never populated.
My plan is to have this logic like this created:
If [TypeMember] = "Social Member" And [LocationNumber] > 4 Then
MsgBox "Associate Member does not have privileges to use other services", vbOKCancel
‘Close the validation form
DoCmd.OpenForm "frm_ConductTransaction"

End If
I tried to write a similar code and it failed. I am unsure what I did wrong.
If you put the correct code and correct logic, let me know where should this code be incorporated.. on open or on load or on current….? I am unsure on this one.
Thank you again for your outstanding contribution.
I have attached the database sample for your review. If you can send some corrections or samples back, I will be very much appreciated. Thank you.
1. Where you put the code to limit members being added depends on how you add members. If you are using a button to add a new member, then it should go there. Otherwise, the Before Insert event of the form would be a logical place.
. First let me correct some nomenclature here. You don't create unbound "fields". Fields are logical divisions of a record and are not bound or unbound. What you place on a form (or report) are CONTROLS. Controls may or may not be bound to a field in a table. Some controls cannot or will not be bound to fields (i.e. a box or line).
Now the way I would handle this is as follows. I would create 2 unbound comboboxes. One to the member table, the other to locations table. The Rowsources would look like this:
cboMembers: SELECT MemberNumber, MemberLast & ", " & MemberFirst AS MemberName, MemberType FROM tblMembers ORDER BY MemberLast, MemberFirst;
cboLocations: Selection LocationNumber, LocationName FROM tblLocations Order By LocationName;
In the After Update event of cboLocations I would use the following:
If Me!cboMembers.Column(2) = "Social Member" And Me!cboLocations > 4 Then
MsgBox "Associate Member does not have privileges to use other services", vbOKCancel
‘Close the validation form
DoCmd.OpenForm "frm_ConductTransaction"
End If
Thank you for your reply. I have been out due to sickness.
Based on your recommendation, I put in two unbounded fields with the combo boxes. When I selected a member ID and location that is greater than 4, the message box did not display.
Ofollowed your instructions. This is my row source on the cboMembers:
SELECT MemberNo, Lastname & ", " & Name AS MemberName, MemberType FROM tbl_Members ORDER BY Lastname, Name;
(The field names are a little bit different from your example, because the actual fieldnames used in the table.) I checked the SQL queries. It creates three fields. The actual table has more than three fields and the MemberType field is the second field of the table.
My row source on the cboLocations:
SELECT LocationID, Descriptions FROM tbl_Location ORDER BY LocationID
(I did not use the location descriptions because the sorting will be off.)
I put the code in the After Update events for the location.
When I tried to enter the information, it allows me to go directly into an entry form. Even though I switched / changed the code from Me!cboMembers.Column(2) to Me!cboMembers.Column(3), it did not stop or pause for the error message to pop-up.
1. What did I do wrong?
2. My original intention when the ID is entered, I will see the display of Name, Last Name and MemberType somewhere. What codes should I write on -> after update event... to have this information displayed?
Thank you again for your time.
The first question is What type of field is Member Type? Does it store the actial text of the member type or a code. The point being that the If statement has to match what is stored in the field.
If that doesn't solve the problem, can you post a copy of your database so we can take a look.
Hi ScottGem,
The memberType field is the text field. It came from another table called, "Tbl_Type". There are only two records in there, which are "Full Member" and "Social Member". There is one field in this table, called MemberType.
In the tbl_Members table, I used this field as a look-up to the Tbl_Type.
The database is in the development stage. I have not completed it yet. You can look through the attached copy.
If you let me know what I did wrong, I would be very appreciated.
1. There is a form called, frm_Notifications. If the user adds more than the number of records specified in the Module, it should generate an error. I changed the number around and it still accepted the entry.
2. frm_TransactionCheck will check the MemberID and the location. The logic that you presented is working. What I would like to see is to have the membership Name and location descriptions appeared, "after update" events.
I do appreciate if you can check the logics of the database. Again, nothing is working yet. It is still having bits and pieces together.
Thank you for your time.
1. Ok, I see what's going on. This goes back to what I said about where the code should go. What I would do is change your function as follows:
unction AddNew() As Boolean
If DCount("[MemberType]", "tbl_members", "[MemberType]= 'Full Member'") >= 2000 Or DCount("[MemberType]", "tbl_members", "[MemberType]= 'Social Member'") >= 1000 Then
MsgBox "You can't add more than 2,000 Full Members or more than 1,000 Social Members", vbRetryCancel
AddNew = False
AddNew = True
End If
End Function
Then I would use that function in the following ways. In the Add New button on frmNotifications I would change the code to:
stDocName = "Frm_AddMembers"
If AddNew() Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
In the Before Insert event of (replace the macro) Frm_AddMember put:
Private Sub Form_BeforeInsert(Cancel As Integer)
If AddNew() = False Then
DoCmd.Close acForm, "frm_Addmember", acSaveNo
End If
End Sub
If the member count exceeds the maxes, it won't even open the form from frm_notifications. If you reach the max while in Frm_AddMember, it should prevent insert and close the form.
2. The following code expression will display the member name after seleting the number:
Me!Text20 = Me!cboMembers.Column(1)
You can use a similar code for the Location, but the code you have no opens a new form so it seems unnecessary.
Thank you so much for customizing and tailoring the forms. I have one minor thing to do based on question # 2. This one is in the frm_TransactionCheck.
Ocreated another unbound field, like the Text20. I called it Text21. If I would like to have the MemberType appeared after the field cboMembers has been updated, why does this code not work? The display should be either "Full Member" or "Social Member."
Private Sub cboMembers_AfterUpdate()
Me!Text20 = Me!cboMembers.Column(1) 'This will display the Member's name'
Me!Text21 = Me!cboMembers.Column(2) 'This should display the Member's type either "Social Member" or "Full Member"
End sub
In the present, the Member's Name appears after information filled into the cboMembers box.
I am unsure whether it limits the display to be only one field or not. If it is, what will you suggest to have the Member's Type information displayed, after cboMembers field has been updated?
Aside from that, everything else is working like a charm. Thank you again.
Are you sure it was name Text21? Unless you renamed it, it probably was Text28. Otherwise I see no reason the code wouldn't work.
Hello ScottGem,
Thank you for your response again.
Odecided to rename the field from Text20 to "NameMember" and Text28 (or Text21) to "Type" in order to clarify the problem.
Private Sub cboMembers_AfterUpdate()
Me!NameMember = Me!cboMembers.Column(1)
Me!Type = Me!cboMembers.Column(2)
End Sub
I have attached the MDB file for your review. I do not know why it does not work. I do appreciate your helping in this matter. We are almost there. Thanks again.
Found it. You have the column count (format tab) set to 1. change it to 3 and it will work.
Thank you so much. You are outstanding. I have the forms that are working now.
I do appreciate your time and tenacity to assist us by completing this project.
Thanks again. -o! yayhandclap.gif frown.gif
Glad to assist
Hello again ScottGem and other experts,
After submitting the form to my boss, she stated that it would be nice to have multiple product / services located on one entry form. The old form that you had assisted could accept one product per transaction. She would like to see whether we can have several products listed on a single transaction.
What I did:
1. I created another table, called tbl_TransactionDetails. I used the Transaction Number + ProductCode as my primary key. I linked the relationship toward this new table to two old tables.
2. I created a query called, qry_TransactionDetails. I plan to use this query to build a subform. My subform called "frm_TransactionDetailSubform." I am trying to link the relationship between TransactionID on my main form and TransactionID of this subform. I used the wizard and could not see the explanation of all boxes.
1. When I embeded the subform into the mainform. I still could not get it to work. I received error message that I did not know why it happened.
1.1. Open the frm_TransactionCheck, select some of the number such as 3 and member ID (select anyone.)
1.2. A new form is populated. Select "Transaction Conducted by", use just one number. It will pull the name of the employee.
2. My intention is to have the Transaction ID from the subform be the same as the transaction ID in the Main form. Look like it does not recognize the number because it has the error "#Name?"
3. No entry could go further because the system stated the errors generated by some tables. I could not see them at all.
What I would like to accomplish:
1. When entry, the Transaction ID from the subform will be automatically populated, based on the number appeared on the main form. It should not limit me how many entry I will have.
Can you assist? I have enclosed an updated file for your review.
I do appreciate if one additional post could be made. Thank you.
I'm not sure I follow your work flow. But you have several problems. First, your tbl_TransactionsDetails table needs an autonumber primary key and TransactionID and ProductCode should NOT be primary keys, they are Foreign keys. Second your subform should then have the TransactionID control bound to TransactionID, not referencing the control on the main form. The Mainform/subfrom link will automatically fill it in.
lso your mainform (frmTransactionCharge) should NOT be based on a query. It should be bound to the table, you can pull in the Location and member info using DLookups or the Column property of the combos.
Thank you again for the rescue. Everything is working perfectly now. Your advice regarding the change from query to table is working. I have got the database that I am looking for.
Again, this is an enhancement from earlier version. We have a good and great working copy at this moment.
You are the best. Thanks.
Glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.