Fill in a record based on another form Results

Hi all,

I want to populate a subform with data from another query based on specific criteria from that parent form. I'll give you the example.

I have an asset (a car for example). That car is made up of many parts (wheels, doors, etc.) I have the user build this asset and give it a number (serial number if you like). I have the user assign all the different parts to the asset that would be needed to make it. I do this through a form subform (asset and asset detail, linked by asset ID).

Now that they have their asset built, I want to use it in a contract, so I have them create a new contract and assign an asset to that contract (1:1). In the contract details (subform), I want to list all the parts that were needed to build the asset I have selected (basically fill this subform with the information from the asset details). I also want them to be able to add and subtract any other items from inventory that they want for this contract. Then when I save the contract, it would have all the parts (the ones from the asset and any others they choose), saved as the contract details.

I know that I cannot use the asset details directly because it would change the asset details if I let them add or subtract so I know that I need to have a copy of that data added to the contract details. That's where I'm stuck. Both the asset details and contract details pulls their part's list from inventory.

I hope this is clear and makes sense. I have attached a sample database.

Thank you in advance!

I have a Form to make, and I have only done the most basic of form work in the past. Honestly I dont even know enough to use the proper terms when asking this question, but Ill do what I can:

I have an existing Table A in my db. Each record corresponds to an instrument being installed in a job, and is labeled using a combo of fields: Area, Tag Prefix (textual), Tag (numeric), and Tag Suffix (textual), so an instrument might be labeled PV-18B in Area 200.

Table B is a 1-1 table to A and is being used to construct Datasheets for each instrument. Each each datasheet will have 64 fields of data related to the spec settings of the instrument.

The Category labels of those 64 fields in the datasheet will differ based on the type of instrument (which is stored in Table B). The type is a field in B and the category names themselves are stored in a third Table C.

Now, I already have a query and report that will gather all this info together and print it out, with one sheet per instrument, data from B filled in, with Tag labels from A, and Spec Categories from Table C.

Now I am trying to make a Form to make Data Entry easy. Ideally, I would have a sheet that looks like the 1-page Report, with combo box controls at the top to point the data to a specific, pre-existing record in Table 1 and another Combo box control to select the Instrument type (which would then automatically populate the Spec categories). Then you just have to go through the 64 fields and fill in the different settings as needed.

If I have to break these functions into separate windows (sub-forms?), so there is a separate screen for record/instrument selection and for setting entry, Im open to it.

Any suggestions for a n00b as to how to go about this?

I am a volunteer at an elderly care home. I am trying to help one of our supply employees with a form he uses called DME Check Out which writes to the DME Check Out table. The form currently has three fields: Equipment ID number (FAS number in the DME Check Out table and the DME Master table) Patient ID Number (ID# in the DME Check Out table and the Client Data table), and Start Date (Start Date in the DME Check Out table). In order for the user to enter the Patient ID Number they have to look at another data base ( VistaKEANE) which contains all of the Patient information. Once the information is entered into the form, the user clicks the >* in the lower left corner of the form and the information is written to the DME Check Out table. The supply person then enters the Patient First Name and Last Name manually into both the DME Check Out table and the Client Data table. He also has several other fields and check boxes to fill in. In addition he has queries that look up the patient by last name or by ID#. We want to replace the Patient ID data entry field with a list box that gets its information from the VistaKEANE database directly and writes it to the DME Check Out table. The table that VistaKEANE uses is called the br549_Resident table. I have successfully replaced the Patient ID Number data entry field with the list box that shows the patients last name, first name and ID# but the form does not provide the >* for the write process. Please note that we are not professional developers, so there are spaces in the field names and table names.

The Record Source for the form currently in use is:
SELECT [DME Check Out].[ID#] AS [DME Check Out_ID#], [DME Check Out].[FAS Number], [DME Check Out].[Start Date], [Client Data].[First Name], [Client Data].[Last Name], [Client Data].[ID#] AS [Client Data_ID#], [Client Data].[Room No], [DME Master].[DME Type] FROM ([DME Master] INNER JOIN ([Client Data] INNER JOIN [DME Check Out] ON [Client Data].[ID#] =[DME Check Out].[ID#]) ON [DME Master].[FAS Number] =[DME Check Out].[FAS Number])

Note that the ID# is a number in the DME Check Out table and the cooresponding field in the br549_Resident table is health_rec_nbr and is text so I added a field to the DME Check Out table called Text_ID and used a query to update the table.

I tried the following record source on the new form with the list box fields declared in a VBA module. Note that column (0) is res_snbr, the key in the br549_Resident table which came into the list box automatically when I used the wizard to create the list box.
SELECT [DME Check Out].[FAS Number], """&Me.List18.column(1)&""", """&Me.List18.column(2)&""", """&Me.List18.column(3)&""", [DME Check Out].[Start Date], [DME Master].[DME Type]
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];

This wrote to the table but understandable did not write the correct last name, first name and Text_ID, so I tried the following as a test to see if I could write the correct last name.
SELECT [DME Check Out].[FAS Number], [DME Check Out].[Last Name] AS ""&Me.List18.column(1)&"", [DME Check Out].[First Name] AS """&Me.List18.column(2)&""", [DME Check Out].[Text_ID] AS """&Me.List18.column(3)&""", [DME Check Out].[Start Date], [DME Master].[DME Type];
FROM [DME Master] INNER JOIN [DME Check Out] ON [DME Master].[FAS Number] = [DME Check Out].[FAS Number];

I got the error message The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Please help.

I fear I may have stumped the community over at Utter Access, so I thought I'd post here as well. I'd really appreciate some feedback. Due to being a new member, I can't post links so here's my original post:


This also has to do with "Tables + Relationships" so I apologize if I've misplaced this thread.

My setup -- (a trimmed and zipped version of my db is attached)I'm an amateur Access enthusiast trying to write a contact management database at work. We are a midsize construction general contractor and this db is to store contact information for our 3000+ subs. One main goal is to be able to query subs according to categories denoted by MasterFormat. I set up a many-to-many between the companies table and the MasterFormat sections table as well as the subsections tables. Then I created datasheet subforms of the sections in a company details form. Those subforms are based on queries that pull from the linking tables in the m2m relationships.
My problemThe current set up works if I run an update query that creates linking records in the m2m tables. However, with the number of companies I'm trying to track, the db size is quickly growing out of control. With just the 46 concrete companies I'm designing the db with (eventually becoming over 3000) multiplied by 35 sections, plus 46 companies multiplied by 1326 subsections, and I've already got over 62,000 records and a db that's over a gig in hd space! I don't anticipate that scaling well with the full number of companies.
My QuestionThere's got to be a more natural method of doing this using relationships and I'm completely stumped. I like the general idea of what my form does -- a list of sections with checkboxes. But I'm completely open to suggestions. Is there a way to get the datasheet sources to list all the sections without records in the m2m tables? That way the only data being stored (and taking hd space) are the sections the subs are actually related to (most often it will be just a couple). How might I have to adapt the form to a different table/relationship solution? Or is my original method really the only way to go and I should be migrating the back end to the SQL server?
A few notes: I emptied the m2m tables to save room. If you want to populate them, I have buttons on the "Scratchpad" form that will do so. Be forewarned it will take a few minutes and eat a gig of space. Also, the company information contained is all public domain -- nothing private is being shared.

And here's relevant follow-up:
Jeff B. worte:
Quote: If I'm following your description, you are creating ... "dummy" records in your M2M table, one for each possible combination of sub & category. And, as you pointed out, this means there are a lot of empty, unused, unnecessary records.

Why did you decide to create one record for each possible combination? That is, what is the underlying business need that will be satsified by having all those "empties"?

... and if there isn't a business need, don't do it!

Instead, it sounds like you want to have one sub associated with a limited set of categories. Why even set those up "in advance" (i.e., a "dummy" record until filled)? Another approach would be to only create those as they are relevant. After all, won't you have to fill in the empties anyway? I may not be seeing the big picture, but it seems like you'll do the work to fill in Sub-A's records one time or another, so why bother creating a dummy before you need the record?

... or maybe I just am not getting it!

Good luck! And I responded:
Quote: The whole point of those "dummy" records was purely for subform functionality. There needed to be a set of records in order to present the user with a list of possible sections to select with checkboxes next to them. Your question is exactly what I'm getting at. There has to be a better way to do this. But I don't know another way to create a form (or possibly alter the relationships) that can do what I want. The kicker is that MasterFormat isn't a static thing. They release updates and changes every year or so, so I was trying to account for that as well. So the short answer to your question is that they filled the recordset for the subform's datasource. Quote: Thank you very much for engaging me on this. A note about the attached db's on this thread: in their current state they don't do exactly what I said because I had already started to tinker with the problem. So you'll find weird little things like specifying the company ID in a source's query, etc. But most importantly you can see the datasheet subform's in question on the Company Details form. With the original design the user saw the complete list of sections available to checkmark, which in turn filled the combobox with a list of sections selected. Then selecting from that cmb brings up the other subform for the subsections.

New to Access and trying to develop forms to use database as opposed to filling in tables directly.
Simple database based on Microsoft's Contact List having added a "Visit" table which has three fields - VisitID (key), _ContactID (foriegn key to link to ContactID, Notes (memo field to record visit details)
I have renamed Contact Details form to frmContactDetails and included several tabs to show grouped infomration e.g. Business Details, Contact Details, Operational Details, Business Plans, Visit Details and Attachments.
My idea is to be able to scroll through the list of contacts (which works fine) and by opening the Visit Details tab scroll down the list of visit dates and for each visit date highlighted the relevant notes for that visit are displayed in the visit notes subform (this is not working)
My issues are with the manner in which I am referencing the subforms on the Visit Details tab, in particular the visit details (i.e. memo field).
I have created two subforms:sfrmVisitDates: RecordSource - VisitsQuery
Caption -Visit Dates Subform
Default View - Datasheet

sfrmVisitDetails: RecordSource - Visits
Caption - Visit Details Subform
Default View - Single Form
On the frmContactDetails (on the Visit Details tab):
sfrmVisitDates: Name - VisitDates
Source Object - sfrmVisitDates
Link Master Fields - ContactID
Link Child Fields - _ContactID
Event: On Current - Event Procedure as follows:

	Private Sub Form_Current()
' This code created by Form Wizard.
    Dim strParentDocName As String
    On Error Resume Next
    strParentDocName = Me.Parent.Name
    If Err  0 Then
        GoTo Form_Current_Exit
        On Error GoTo Form_Current_Err
    End If
    Exit Sub
    MsgBox Err.Description
    Resume Form_Current_Exit
End Sub

sfrmVisitDetails: Name - VisitNotes
Source Object - sfrmVisitDetails
Link Master Fields - [sfrmVisitDetails].Form.[VisitID]
Link Child Fields - VisitID
When opening (running) frmContactDetails I am presented with the following "Contact Management Database can't find the field 'sfrmVisitDetails' referred to in your expression. Clicking on OK them displays another dialog box Enter Paramerter Value - sfrmVisitDetails.For.VisitID [text box] [OK] [Cancel].

I have read through numerous threads but still can't see the error I am obviously making. I have tried swapping the reference sfrmVisitDetails with [Visit Details Subform] with no success.

I hope someone can see where my error, which I'm sure is very obvious to those more experienced.
Thanks Peter

I spent all day working on this yesterday and I think I've got a pretty good solution. So I wanted to share my findings with others in case it might help anyone else. I was also hoping that people might offer suggestions on how to improve on what I've got or ideas of other possible solutions.

Here's the problem: I've got a form with 4 unbound "drill down" combos, each of which changes its row source based on the value of the previous combo, that I'm trying to convert to continuous form view. To make it more complicated, combos only become visible after a selection is made in the previous combo and the depth of the drill down varies per record (i.e., some combos won't ever be made visible on some records).

Here's my solution, thus far:

1) Create a text box to cover each combo and give it an On Enter event that sets the focus to the combo behind it. (These text boxes will be referred to as "display text boxes" from now on.) I first found an example of how to do this on Allen Browne's web site, but it seems that this solution is well-documented in many other places as well. (Allen Browne's example doesn't actually deal with combos, but it was easy to adapt it to my needs.)

2) Fill the display text boxes with values based on bound data. In my case, all four unbound combos serve to drill down to AccountID, so the values of the text boxes can be generated based on the current record's AccountID. I used a (hidden) combo box (cboAccountID) with several columns to accomplish this. For example, the control source for the first display text box is set to


3) Create Form_Current event to set all of the unbound combos to match the values in the display text boxes in front of them. In this case, I also took this info from the columns in cboAccountID. In Allen Browne's example, he sets the value of the unbound control only when he needs to access that control, but I'm doing it in the On Current event because I can't set one combo without also setting all the combos before it. Anyway, that's the way that made more sense to me at the time.

Hiding combos
4) Create text boxes to cover each combo box that you want to hide. (In future I will refer to these text boxes as "hide control" text boxes.) Set background transparent and set font color to match the form background. Set font to Terminal and font size to 127 (as large as possible). Set control source to


Substitute for chkHide any check box or True/False expression based on bound data. ("" in Terminal is a black box (better than other black box characters I tried because it didn't leave any blank space between characters, at least not on my system).) Thanks to Kevin Gray's ColorCon example for the "=IIf(...,'')" stuff and to Pavlo Pedan's "Conditional Formatting (Visibility)" example for the idea of how to hide controls on continuous forms.

That seems to get us most of the way there. The main obstacle left to tackle is that the current record should display differently than the others. As it stands, the combos are always covered by the text boxes in front of them. So if I enter something in combo1 and then jump to combo2, combo1 immediately becomes covered up by its text box and I can't see the value that I just entered. So we need to set it so that the appropriate unbound combos are always visible for the current record.

Formatting current record:
5) Create a hidden unbound text box to store the primary key of the current record. Add code to the form's On Current event to update the value of this text box. In my case, the code looks like this:

	Me.txtCurrentRecord = Nz(Me!SpltID, 0)

I also had to put this code in a couple other places in my module. For example, when you're entering a new record the Form_Current event will set txtCurrentRecord to 0, so you need to update it when your record is given a new primary key value. Now you can format the current record using the following expression:


(Use your primary key in place of SpltID.) Thanks to user pere_de_chipstick at for this solution.

6) Set the background of the display text box to transparent and create another text box of the same size in the same location. Set the formatting for this text box the same as that of the "hide control" text box. Set the control source to the following:


(Again, substitute primary key.) Position this text box behind the display text box but in front of the combo box. Now all records will display the same as before except for the current record, which will always show the unbound combos (except where the "hide control" text boxes are filled).

7) Make sure that your display text boxes are always either empty or the same as your combos for the current record, otherwise you'll see the text of both controls on top of each other. I guess this is another reason that I update all the combos to the values of the display text boxes in Form_Current. The other thing that I do is clear or update the value of the display text boxes when I change the value in any of the unbound combos.

8) (Optional) Create transparent command buttons on top of the "hide control" text boxes with On Click events that set focus to the combo boxes (if the combo boxes are visible). I did this because I didn't like that when the "hide controls" text boxes were filled (i.e., you couldn't see the combo behind them) the cursor would still change from an arrow to a text cursor, even though the user didn't see any text field to edit. Now it stays an arrow for those fields. (This is still not perfect because it should turn to a text cursor when the combo is visible, but I didn't know how to fix that.)

OK. I think that's it. I'm pretty sure I didn't leave out any important steps. See attached example database for more info. The example database was pulled from an actual database I'm working on, so it's possible that it will have broken references, but I'm pretty sure it's good. As you can see, the form that I created is for entering split transactions in a bookkeeping database. The old version of this form didn't use continuous forms and was therefore much simpler to design, but the users found it confusing so I'm going to try this to see if it makes more sense to them.

So... is this useful to anyone else? Is the example too specific? Should I have broken it into separate threads?

More importantly (to me), does anyone have any suggestions on how to do this better? Or how to minimize flicker? Or how to design a better form for entering split transactions?

I wanted to link to the examples I cited, but I'm too much of a newbie to be allowed to include links.

Hi All,
I have a pretty simple db that currently has one main data table with things like Employee name, class, class date, scores, etc. I am trying to make a form where my trainers can add a new "roster" of people when a new class starts. (These will record to the main table and then the trainer can later open another form that displays all of the records for score input, etc.).

Right now I have a form that displays Trainer Name, Class Name, Class Start Date, and Employee Name. When a trainer goes to put in all of the names for the roster, the first three fields will stay the same. I want to have a button that when clicked opens the next record but fills in the first three fields based on the selections made from the current form.

I'm open to VB or Macro... any help would be appreciated!!


(Hope this is the right forum)

I have a database which I created with about 1000 records. (Records imported from Excel). These records are all in one table, I have another couple of tables with no data waiting to be filled via a form.

My problem: Now that I have added more records to my main table approx. 40,000 (imported from Excel) and my form still only recognises the 1000.

I have spent a lot of time creating forms based on a main query that connects my 3 tables. My fear now is that the problem is a relational one (is it?). If so, does this mean that I have to create a whole new set of forms based on the / a new relationship.

There is no unique data in my tables and so Access has generated an auto number field ID. This field is in each table and is how my database is related. Does anyone know if this is an issue?

Currently I have got round the problem by copy and pasting my ID field from the main table into the other tables. This works but, the same error will occur everytime new data is added. I thought being of type 'auto number' it should do this for me. A long term solution anyone?

Help would be greatly appreciated,



I have to thank you guys right off the bat. I have been searching for a way to fill one field based on the value of another and found that answer here. So, I decided to join in. This is that best source of information I have found for Access. Many pats on that back to the founders!

Now then, I used Dlookup to automatically fill a text field based on a selection I make in an option group. What I would like to happen is the data show up when I select the option, instead of updating when moving to the next record. Here is what I have to this point and it works great to populate my Finding field after I move to the next record. I put this in the "On Current" event of the form property.

If Me!ComplianceLevel = 2 Then
Me!Finding = DLookup("NoneFinding", "tblCAPProtocolQuestions", _
ElseIf Me!ComplianceLevel = 3 Then
Me!Finding = DLookup("PartialFinding", "tblCAPProtocolQuestions", _
Me!Finding = Null
End If

If I choose one compliance level, I get a finding and another compliance level gives me a different finding. I would like to update the field when the selection is made so I can enter some comments about the finding. Also, should I improve what I have so far? Thanks.


I am constructing a small database to house details of candidates that are invited to attend interview, whether they pass the interview and if so when they start work and on which shift. Nearly all of the fields relate specifically to candidates - as a result most of the information resides in the candidate table.

I have attached a MS Excel spreadsheet listing the fields in the tables so far (not including the lookup tables for title, gender,

There is also a table used to store information about the shifts. This database will only ever hold up to 6000 records so it isn't critical that the design is perfect - even so I would like to construct it as well as I can. If anyone has any comments about using more than 2 main tables to store the info I would be pleased to hear.

I also have a couple of specific questions,

1. At the end of the candidate table there will be 2 yes/no fields and a memo field. I would like to be able to have a form based on a query that lists values with a yes in the first y/n (field1 in example attached) but field 2 and memo are blank. When the 2nd y/n is marked as y the 'required' property of the memo field turns from no to yes (and vice versa if possible) this would be based on an update event - but I am not sure what. Any ideas ? as these fields are filled in they will disseapear from teh query 1 result but a second query willpick them up - these values are held in a subform datasheet (is this possible).
2. The memo field is held in the candidate database (relates to clarification on why a placed candidate exited a job early). As such it will only be filled in by those candiates who do leave early and is not always required. Does this mean it is best to hold this info in another table so space is not wasted ? as I mentioned the DB will only house 6000 records tops so space is not a critical issue.
3. Do I need to worry about checking definitions of relationships between lookup tables and main table ? the relationship between shifts table (maybe 15 shifts) and candidates (several thousand) will presumably be 1 to many as a shift can have many candidates working on it ?

any comments appreciated,




I have a form, frmUsers, based on tblUsers which stores info about usernames, userlevel, password. Only one Admin user has access to go to this form and add/delete users. It works fine. I have another form, frmOpp, that has a combobox that display the usernames which is based on tblCaptureLead. This table has just one field, username, which was created for the combobox. Right now when the Admin user goes and adds a user in frmUsers form it only updates tblUsers. I wanted that when a new user is added, tblCaptureLead should also be updated at the same time. Because when a user adds a new user and then go to frmOpp they don't see the new user in the combobox. Is there a way to update tblCaptureLead at the same time?

This is the code that I have behind the update button on frmUsers

	Private Sub btnSubmit_Click() 'save record
On Error GoTo Err_btnSubmit_Click
'check for req'd fields
If IsNull(Me.USERID) Or Me.USERID = "" _
Or IsNull(Me.txtPassword) Or Me.txtPassword = "" Then

MsgBox "You haven't filled all the required fields to create a new user"

'make sure password is 8 or more characters
If Len(Me.txtPassword) < 6 Then
MsgBox "The password should be at least 6 characters"
'if so save record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
End If
Exit Sub

MsgBox Err.Description
Resume Exit_btnSubmit_Click

End Sub



I am trying to create a land and property database which will consist of a series of sites at the top levels.

Related to each site there will be the following sub tables
Housing Database
Property Database

I have created a query that shows me the names of the sites and how many related records there are for each of the two sub tables.

I would however like this described within the opening form (based on the query) graphically.

My idea is design a form with two coloured boxes one which relates to housing database records and another for porperty records on the site name form.
If there is a sub record a box will appear in a particular colour if there are no sub records the box will be uncoloured.

I am a basic programmer but the kind of thing I was thinking about was this.

If [Houserecords in query] > 0 then
text22.back colour = 20 ' or a number relating to red
text22.back colour = 1 ' or number relating to white
end if

and the same for Property records in query
except the text field will be the next along say 23.

The opening form will show all the sitenames and this needs to be done for each record in which case where within the properties of the text field do I put the code.

On load ??? In which case how does Access know to go through each line on the screen and work out / fill this out for the records.

Any advice would be appreciated.


I have a form that has a picklist which is pre-filled based on user login upon open. I want the data on the form filtered by that field. If the user chooses another entry from the pick list, I want the form to requery and filter by the new value. I have put the following code into my On Update property of the picklist: forms!frmName.Requery. I have the following in the filter property of the form: =[picklistname]. However, it doesn't seem to be working. The records are not filtered at all. I tried basing the form on a query instead of the table with the criteria for that field as forms!frmName!picklistname. However, that seems like a circular reference to me and it doesn't work anyway. When the user chooses a different item from the pick list, the records change, but the pick list doesn't change. Can anyone offer some advice?

I have a basic design question that I am not sure how to address.

I am trying to build a simple data entry database with a form to take input from the user, store the values in a table. Once the data is gathered into the table I want to use this table to print a report of each record (entered using the form before).

To achieve this objective, I made a form (frminput) with some text fields. Most of the fields on this form are Bound fields to a query (qrymaster). One of the field is a Combo box (whose value is shown from another table). I have designed the RecordSource of this Form to be a Query (qrymaster).

This is a basic Data Entry form where the user selects the Combo box item and based on what he selects, some of the fields in this Form gets pre-filled. The rest of the fields on this form are bound to the query "qrymaster" and the user has to type these fields manually.

Now, I want a Save button here that would save all the values on this form to the table "tblmaster". How do I do this efficiently keeping in mind all the normalization laws on the database?

At this time, behind the Save Button, I have included a SQL statement to insert all the field values into the table "tblmaster".

I am sure there is a better way to do this. Can someone point me in the right direction please?


I've almost completed the DB I'm working on, but still have a few loose ends to tie up that I can't seem to figure out. I've spent many hours already on this forum searching for solutions, and have tried a few different things, but I still can't work out these bugs.

First, I need my form to open with the latest records filtered first, so that the most recently added records will be easiest to find and edit. I'm not sure how exactly to do this. I've tried a run query when the form is opened, but I still need all the records displayed or accessible. The form is called frmDenial, and I would like it to be looking in the DateLogged field of the form. Any suggestions?

Second, I have command buttons that will export data in the fields to MSWord Templates via bookmarks. The button runs well by opening the document, inserting the data, and printing. I have also tried several different code syntax to close word once it is done, but it is still staying open after printing. Here is a sample of the code:
Private Sub Print_Letter_Click()
Dim objWord As Word.Application
'Start Microsoft Word 2000.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = False
'Open the document.
.Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial ProcessesKAN Not Nec or")
'Move to each bookmark and insert text from the form.
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))
.Selection.Text = (CStr(Forms!frmDenial!MemberNumber))
.Selection.Text = (CStr(Forms!frmDenial!MBRAddress1))
End With
'If a field on the form is empty, remove the bookmark text, and
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
Set objWord = Nothing
Exit Sub
End Sub

Finally, I have two cascading combo boxes set up that will auto pop related fields based on the selection made. But, if an item is not in the list, I would like the user to add it to the linked table to appear in the list. I also have this working well, with a pop up asking the user if they want to make the addition, type in the new item, and add to the list without requiring the user to refresh or exit then re-enter the form. But it will only add the item name, and not the item description (another field in the form and another column in the table). How can I modify the code to prompt the user to enter these other details? I can link it to a pop up sub form to enter the data, but if possible, would rather the boxes pop up to have the user type in the data. Here is the code I have so far in the NotInList Event...
Private Sub DrugName_NotInList(NewData As String, Response As Integer)
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Drug" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Drug to the current Database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new model?") = vbNo Then
Response = acDataErrContinue
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblDrug", dbOpenDynaset)
On Error Resume Next
rs!Drug = NewData
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Response = acDataErrAdded
End If
Set rs = Nothing
Set DB = Nothing
End If
End Sub
The two other colums in the tblDrug that I need the user to be prompted to fill are Denial Reason (column 3) and Alternative (column 4). What would be the best way to accomplish this?

I would really appreciate any help or suggestions with any of these problems. Thank you so much!

I have a subform (on a search form) that is based on a query. What I am trying to do is when the user dbl clicks any record in the subform, another form (the edit form) opens allowing the user to edit the information for that record. I have created a macro to open the form and have filled in the Where clause to view the current selected record and have set the On dbl Click properties of the subform to invoke the macro. This all works fine if I open the subform individually in the database window but it does not work if I open the search form (that contains the subform). I know that there is a way to do this, I just can't remember how! I think that it has something to do with the Where clause in the macro, something about showing the whole path from the subform to the search form...
Does anyone know???

I have a table that I'm working with and explaining maybe be a little tough for me.
The table has USER_ORD_NUM and INVOICE_NO

The table I have shows one User_Ord_NO that matches to an invoice number. Afterward I have a series of parts that fill in that only show the invoice number with the USER_ORD_Number being blank. I tried to filter the results based on USER_ORD_NUMBER where the number contains the word "AS" which in no way will work for my dilemma.

Is there any way I can rifle through the records find the USER_ORD_NUM match it to an INVOICE_NO and then change filtering to use INVOICE_NO which would then create my report. I would like to have one form with two buttons one to print a report without "AS" orders and the other to print only "AS" orders.

I would use just the INVOICE_No but the invoice number doesn't have any keywords I could use to separate all orders from "AS" orders. The access program I'm working with was created some time ago by another person and a complete rework would take too much time.

The reason I need to do this is because our assembly (AS) orders are pulling in all parts that go into making the assembly with a UNIT_PRICE of 0.00. So the current report will not work for invoicing because of all the line items being shown and causing confusion with our customers
THE DATA IS BEING DATAMINED FROM AN OLDER ERP SYSTEM into a table within access. The USER_ORD_NUM isn't carried entirely accross all tables in the ERP system. However as you can see with the example the INVOICE_NUMBER carried nicely.

10AS111 | 111111 | 123 | 23 | 230.00
NULL | 111111 | 222 | 20' | 180.00
NULL | 111111 | 333 | 10' | 100.00
10FB21 | 222222 | 444 | 20' | 150.00
NULL | 222222 | 555 | 20' | 170.00
NULL | 222222 | 666 | 10' | 100.00
10FB22 | 333333 | 444 | 20' | 150.00
NULL | 333333 | 777 | 5' | 150.00
10AS333 | 444444 | 888 | 1' | 50.00
NULL | 444444 | 999 | 2' | 60.00


I'm very new to all this, I'm trying to write a DB to record lab results within a specification limit, and run a report to print the results on a certificate based on an automatically generated batch sample number.

In tables I've managed to get a drop-down list for the 'product type', and another for a list of the tests associated (multiples allowed) with that particular type fluid type. What I'd then like to do is add result/values for each of the tick boxes selected. Currently I just get a list of the tick boxes selected.

Sorry, if this isn't too clear! Currently I have:

Name (select from other table)
Fluid Type (select fluid 1 to 5 from another table)
Test (test 1, test 2, test 3, test 4... ...test 8) this is where I can select multiples from tick boxes

I'd then like to type a result for (e.g.) test 1, test 4, test 6 or whatever tick box was selected. Is this possible??

I realise a workaround would be to list all the possible tests as columns and just fill in the relevant column.

Better still would be to select a fluid type, and this opens another form with minimum and maximum allowed values, where I could enter the data - can I do this and keep unique IDs for each sample?

This is with access 2010, I'm starting to find my way a little but need lots of help!!

Many thanks in advance.


I will warmly welcome the all advises and help to develop this database design in correct way and proper way.

The Process Flow Chart is attached to have a look for better understanding.

Its a manufacturing process flow chart of Pipes Manufacturing Company where I want to focus from Raw Materials Receipt to Finish Goods Transferring to Warehouse.


1. Raw Materials Receipt. (From P01 - P02):

The different type of Raw Materials based on Purchase Orders (almost 50 Items) received at "P01" and recorded in ERP system.

1.1 Laboratory Tests & Quality Control.
A sample bag is sent to Lab. For Testing the quality of Materials and a Lab. Technician will Test the Materials as Test Code T001 based on the Type of Raw Material, there are Specified elements to check for each category of Materials and especially if it not as per standard values, so a report is submitted to Purchase/Stores for rejection of Material.

2. Raw Materials Preparation (From P03 - P05):

The Clay Raw Materials (two types mostly) processed and milled at "P03" through different stages and converted into required particle sizes and then mixed by appropriate percentage as per given "Dry Powder Body Composition" (Which is issued by Lab.) at P04 and send to next process P05 to add water into and prepare Mixed Clay.

2.1 Prodn. Data Recording and Data Type:
- @ P03: The Control Room Operators record the kgs loaded into Hoppers (4 Units) for each type of material.
- @ P04: The Control Room Operators record the kgs of each type of material mixed according the given compositions of dry powder mix.
- @ P05: The Mixer Operators (2 Units) record the kgs of each batch (normally fixed quantity like 1,400 kgs) and the quantity of water added to this mix (variable quantity) to maintained the moisture standards of Mixed Clay.

2.2 Laboratory Tests & Quality Control.
- After P03: The Lab. Technician took random samples throughout the shift and performs Test T002 and advises the concerns.
- @ P04: The Lab. Manager issues Dry Powder Compositions and that is followed at mixing stage for related products until new instructions.
- After P03: The Lab. Technician took samples of each batch and advises the Mixer Operators to maintain the standard limits of moisture.

2.3 Notes:
- Touch Screen Data Entry at P05 will be the best practice.

3. Product Making and Drying Process ) (From P06 - P07):

The mixed Clay Body is directly sent to the Extruder Machines P03 (3 Units) where 3 different Size of Pipes as per Monthly Production Plan extruded and loaded on Dryer Cars (as a Carrier to take Pipes trough next Process).

Those loaded dryer cars automatically pushed into the dryers (5 Units) as programmed by Product size. The Dryers are programmed at fixed cycle time (70 Hours and can be changed) and also other parameters like humidity % and Temperature.

3.1 Prodn. Data Recording and Data Type:
- @ P06: The Operator of each machine (3 Units) records the production data as, Dryer Car #, Time, Good Pipes, Bad Pipes and any comments during extrusion.
- The Operators also filled in the Quality check sheet which contains 17 Elements to checked and write down after 3-4 hours Intervals.
- After P06: The Line Controller records the Dryer Car # and the time it pushed into which Dryer #.

3.2 Laboratory Tests & Quality Control.
- After P06: The Lab. Technician took daily random small Pcs of samples from Pipes from Dryer Cars and performs Test T04 and advises the concerns.
- After P07: The Lab. Technician took daily random small Pcs of samples from Pipes from Dryer Cars to measure the moisture of Pipes and performs Test T05 and advises the concerns.

3.3 Notes:

- The Dryer cars are same in structure and labelled with unique code number (001-400).
- Looking for Touch Screen Data Entry practice.

4. Inspection, Glazing and Loading Process) (From P09 - P12):

The Dryer Cars comes out from the Dryers and shifted to the Production Line ( 2 Units) where Inspectors check the quality and marked as good pipe , reject Pipes.

The Good Pipes lifted by cranes and dipped into the Glaze tank and shift to the flat forms where a Human loader lift the pipes with vacuum loader and set up on Kiln Cars (as Carrier to take the Pipes through the Firing cycle). The reject pipes scrapped in trash tank and send to scrap yard time to time.

4.1 Prodn. Data Recording and Data Type:

- @ P09/P11/P12: The Operator/Inspector of each Production Line (2 Units) records the production data as, Dryer Car #, Time, Good Pipes, Reject Pipes and reason for rejects, Loaded on Kiln Cars #, and any comments Like any Glaze added to the tanks during that specific Car no.

4.2 Laboratory Tests & Quality Control.
- @ P11: The Lab. Technician took daily random samples of Glaze from Tanks and performs Test T007 and advises the concerns.

4.3 Notes:
- The Kiln Cars are two types in structure and important keep them separate while recording and labelled with unique code number (001-100).
- Looking for Touch Screen Data Entry practice.
- When a dryer car is unloaded and it moves to again Process P06 for loading again. So would be better to know a how many cars waiting for unloading and how main ready for P06 and how many are inside Dryers.
- One Dryer Car will be having less no. of pipes comparing to the Kilns Cars. (Like Product SS200 will be having 21 Pipes on Dryer Car whereas 76 required on Kiln Car.)

- Glaze Supply: The Glaze (Liquid form) is supplied from Glaze section (another process of mixing Raw Materials and prepare Glaze) .

5. Pre-Kiln Dryers and Firing Process (From P13 - P114):

Those loaded Kiln Cars automatically pushed through the Pre-Kiln Dryers (3 Units) and then pushed through the Tunnel Kiln (Firing). The pushing cycle is based on the Firing Cycle (Like Max. 24 Cars per 24 hours and in actual Pushing is 15Cars/24Hrs, Etc).

5.1 Prodn. Data Recording and Data Type:

- @ P14:The Kiln Operator records what time which Kiln Car is pushed inside the Tunnel Kiln.

5.2 Laboratory Tests & Quality Control.
- @ P13:The Lab. Technician took daily random samples from Pipes and checks the moisture of Pipes at this stage and advises the concerns.

5.3 Notes:

- Looking for IPad Touch Screen Data Entry practice so while moving around Operator can enter the time and Kiln Car no.
- Kiln Exit Schedule: what is coming out (which product) from Kiln (Firing) in next 24 hours is very important to schedule the unloading Lines (2 Lines Only specified for products, Like Product-A Cant be unloaded at Line one due size and weights) to arrange the people and etc.

6. Unloading & Inspection - Sorting (P15):

The Pipes are unloaded from Kiln Cars and inspectors sort out based on quality standards and define in three categories, Good Pipes, Scrapped Pipes (Throw out) and Short Pipes (if full pipe is not good so they marked as GA, GZ, GE categories of short length pipes and processed to "P16" for Cutting into short lengths).

6.1 Prodn. Data Recording and Data Type:

- @ P15: The Inspector filled in the sheet either it is good, reject with reasons (list of reasons 6-7) and short length Pipes with reasons (Same of Reject Reasons).

6.2 Laboratory Tests & Quality Control.
- After P15: The Lab. Technician took daily random samples of Pipes and Performs different Test (4 tests) for different elements.

6.3 Notes:
- Looking for Touch Screen Data Entry practice.
- Very Important Area of Process which shows the results of quality of all the processes starting from P03.
- I am sure it will be possible, if we just click the Kiln Car No and would be able to know its sorting results, from which dryer cars loaded and those dryer cars was produced what time and from body mixed batch and also if there is any Lab test performed on those Pipes or before or after throughout the process.

7. Jointing of full Pipes and Short length Pipes (P17):

At this process, the pipes which marked as good by the Inspectors directly shift to the Jointing carousel for Jointing the Socket and spigot. The short length pipes taken from Cutting Section "P16" after some days or as required to joint.

7.1 Prodn. Data Recording and Data Type:

- @ P17: The Operators record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.

8. Packaging & Transfer (P18 P19):

At this process, the pipes which ARE jointed from the previous process packed as per standards (for Product-A, 20 Pipes/Pallet) and shift to warehouse after Quality control check.

8.1 Prodn. Data Recording and Data Type:

- @ P17: The Packers record the information of production performed on that line.
- Looking for Touch Screen Data Entry practice.

8.2 Laboratory Tests & Quality Control.
- After P17/P18: The Lab. Technicians took daily random samples from Pipes and performs different test as per standards.

9. General Attributes in Data Recording.

Here I will mention some general attributes which are required on each process to be part of data recoding with special data requirements of that process.

- Date/Time
- Production Lines/Machines where data is record.
- Shifts (Day Shift & Night Shift)
- Operators (of that Process or Machine)

10. Main Possible Entities & Attributes.

- Workstation (can be called Work station, ID, Description)
- Process (Can be called Operations, ID, Description, belongs to Workstation)
- Employees (ID, Name, Designation, Nationality, Hiring Date, Process)
- Machines (ID, Description, Process, etc)
- Item (Can be Raw Materials, Finish Goods) (ID, Description, Type, Green Weight, Fired Weight, Etc).
- Dryer Cars (ID, Description)
- Kiln Cars (Two Types TK/SK Cars - ID, Description)
- Shifts (ID, Description)
- Lab. Test (ID, Description, Performed at Process, Ref. Standard No, etc).
- Test Parameters (ID, Description, belongs to test, UOM, Standard Limits Min/Max, etc)
*One Test can have more than 2 parameters to be performed
- Reject Reasons (ID, Description, belongs to process, etc)
- Dow Time Reasons (ID, Description, Belongs to Process)
*If there is any down time at any machine, so will be recorded with reason.

May still something is missing but I think it is enough to have the basic idea of the Process.

Thanks you in advance.


Zee Attached Files Process Description.pdf (76.3 KB, 21 views) Process Flow Chart.pdf (62.3 KB, 24 views) Reply With Quote 10-29-2012,07:35 AM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 Seems you have a "corporate/company wide" database application to design that is tailored to your manufacturing processes. The best I can offer is a link to an existing free data model for a Dutch manufacturer. It may not be specific to your manufacturing, but it should give you some guidance on WHAT is involved and the Relationships between Entities.

Good luck with your project.

SO i have a list box that fills based on the following code.....
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL

There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.

DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"

The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID) just opens all of the products with that name up, starting with the first one.....

Can anyoen help me have it only show the specific record?...thanksss

Not finding an answer? Try a Google search.