Many-To-Many Through A Single Sub-form

Sep 21, 2004

Ok, as the title suggests I'm having some trouble making a many to many relationship work using a single sub-form. This is really more of a lookup/combo box question if you think about it.

Here is the situation: I have three tables, ComputerPrinter, ComputerComponent, Components

ComputerComponent is actually a joining table between ComputerPrinter and Components, with some extra fields which differentiate a specific instance of a component.

It also has it's own autonumber primary key as one ComputerPrinter can have more then one of the same component (i.e. my boss has two of the same monitors). Its foriegn key to the Components table is a lookup to the ComponentDesc(Component Description) field. (I know a Lookup in a table? He's a witch!, burn him! )

I have a query that Joins the ComputerPrinter table to the ComputerComponent table, and that is the basis of my sub-form (the main form is bound to ComputerPrinter).

This all works, you can select any ComponentDesc and it will add that component to ComputerComponents, thus a many-to-many done through one sub-form (albeit limited). The problem comes in that I do not want the user to add just any possible component, but rather only components that are not in use (i.e. have no computer or printer attached to them). I have a query that gets these components, but when I change the row source of the combo box to just those that are not in use, all the components that are attached to the computer (or printer) that is currently selected on the main form, and that do not have an instance that is not in use (there is no spares in the inventory) disapear!.

This is obviously due to the NotInList property, so by making the bound column the first, and changing it's width to .1 inch, I though that it might work... instead I get just the componentID's (the primary key of the Components field) and the text column (despite have a width of 1") doesn't show up at all (although the drop down shows the text of the not in use components). No luck

Is there a way to show all the component descriptions of the current computer/printer and yet limit the drop down to only those that are not in use? (in a continous form without messing up the other entries that is)

If anyone at least knows if it's possible, I would appreciate the post.

-mwalts

View Replies


ADVERTISEMENT

Modules & VBA :: Storing A Single Record In A Single Dimension Array?

Sep 8, 2014

Any Single line of code available?

View 2 Replies View Related

Modules & VBA :: Sending A Single Email To A Single Record In A Query

May 15, 2014

I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:

Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)

[code]...

There are two issues I keep running into:

1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.

2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph

View 9 Replies View Related

Max Value For A Single Date/multiple Time/single Tag ID Combination

Oct 18, 2007

Hi, thanks in advance for any help you can offer. I've got a table that has

Date
Time
Tag ID
Power Level

throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day

I've tried using "group by" and max in the query but this gives me all the times throughout the day.

anyway, thanks again for looking

cheers, Shaun

View 2 Replies View Related

Forms :: Setting Focus To Control On Single Form Side Of Split Form

Jun 24, 2013

When I right click a row on the data sheet side of a split form an select "New Record" I want the curser to go to the first field on the single record side. I've placed this in the OnCurrent but it did no good.

Code:

If Me.NewRecord Then
Me!Descrfiption.SetFocus.
End If

Any way to set the focus to the single form Side of a split form?

View 4 Replies View Related

Forms :: Continuous Form - Click On ID To Populate Single Form

Oct 4, 2013

I have a continuous form that displays all my customers .I want to be able to dble click a customers ID and go to there customer details form.

View 14 Replies View Related

Single Form, Multiple Tables...

Apr 11, 2005

Sorry for the newbie question but...

I've got a form called 'frmStudent' which is linked to 'tblStudent'. I have designed it as a tabbed form, the first tab has "Student name", "Student Number", etc. The second tab is linked to visits by the student to a mentor (such as a tutor etc). Here I'd like to have "Date of visit", "Mentor Name", etc.

So far I've been able to get the first tab to work (I'm guessing because it's linked to 'tblStudent'), but can't get my 2nd tab to work as I have no clue on how to link it with 'tblVisits'. Is there a VBA methor or perhaps a nother method to do this?

I'm attaching a link to the sample of what I'm doing (please shift-open into frmStudent to view what I'm talking about). Any ideas and suggestions would be greatly appreciated!

DOWNLOAD DATABASE:
http://www.myfilestash.com/userfiles/sabotage1945/Student_Help.zip

Kindest regards,
Sab.

View 4 Replies View Related

Single Record Preferences Form

Aug 13, 2005

I need to set up a preferences type form. What's the best way to accomplish this. Can I store this data in memory as a variable?

Here's what I need:

Customer Name, CustomerID.

I have been reluctant to put it in a table because this is the only data that I need.

Plx lead me down the best path.

View 3 Replies View Related

Find Record + SINGLE FORM

Mar 30, 2006

Is it possible to create text box for search for records if I use SINGLE FORM?
i try but without effect :(

pls see my database (I want to use for record "Title")

Thanks

View 2 Replies View Related

Multiple Query's On A Single Sub Form?

Aug 15, 2006

Is it possible to display specific Query's in a single subform dependant on a combobox selection? If so how wud i go about doing this?

Any help is much appreciated. Thanx

View 4 Replies View Related

Fields From 3 Or More Table In 1 Single Form

Oct 13, 2004

I have 3 tables, and I want to enter the data into them using 1 big form.
When I use wizard to create form, i manage to add fields from 2 tables, but once
I get to 3 tables, the form ends up not displaying anything. I am just wondering if
anyone can help me with the above problem, thanks in advance.

View 2 Replies View Related

Single Form Adds Several Records?

Feb 9, 2008

Hi all,

I already have a form where you specific the date and the store I've purchased something at. I want to be able to add several products I've purchased to a table and define their price, using the same form, so I don't have to input the date and store type for each purchased product.

So, when the form updates the table, it will create a new record for each product, using the product description and price - defined for each product - and using the date and store - defined once - as elements of the record. Is there a way of doing this?

I'm quite familiar with creating tables, forms and queries with the design wizard - not so familiar with using scripts, but I've had a small amount of experience with manipulating databases using MySQL, so I'm willing to learn anything that may be necessary to achieve what I need.

Bugme

View 7 Replies View Related

Send Single Record From Form

Dec 1, 2014

I would like to be able to place a button on my invoice form that will allow me to e-mail the individual invoice to the person being billed (the e-mail address is included on the form). I created a macro that works to send a PDF file of all of the records and I am having trouble figuring out how to send just the current record. I would also like to have the e-mail address automatically populated based on the e-mail address on the current record.

View 3 Replies View Related

Emailing Single Record From A Rather Large Form

Feb 28, 2005

Hi fellow Access battlers!

I've searched high and low on this forum on a step by step on how to do this and everyone's answer is either vague and assumes everyone is an access wizard, or really complex and codey and assumes every one is an access wizard!

I have a "lead sheet database" and I need to add a button on the form which emails just the contents of that current record. My company still handwrites these lead sheets and faxes them to consultants so I'm trying to bring them into the 21st century! An uphill struggle. I'm a code and VB newb by the way.

I've tried sending the form to a report as other posts suggest, but I get an error message about there being too many fields. So I'm stuck. All I need is for each form record to be emailed to a different consultant each day.

(I've had to rar then zip the database file due to size restrictions. I couldn't get the files size down enough by just zipping.)

Thankyou!

View 3 Replies View Related

Single Number Issue On Filtered Form?

Jul 25, 2006

Hi all,

I have a form (form2) that filters records based on the employee number (in a combo box) that the user selects. At the moment this works fine on double numbers (such as 10, 12, 80 ect) but if I use single numbers it displays to many records. For example if I select Employee 1 it shows all records that start of with the number 1 such as Employees 1082, 11, 1507, 10043.

If I select Employee 1, I only want it to show records for this employee and the same for other single numbers.

Ive posted the database if any one is willing to have a look for me as its difficult to explain it fully, (ive removed irrelevant tables/forms/reports).Form2 is set to startup, Im using access 97

Any ideas on how I can fix this single number issue? :confused:

Thanks

Jim

View 3 Replies View Related

Add A Single Field Multiple Times In A Form

Oct 3, 2006

I hope someone can help me on my problem below:-

A user wanted to key in the data in a single form where she can select where
a particular procedure is located. The procedure could be duplicated and
placed in a few departments.

My tables are as follows:-
tbl_Proc_Dept:-
ProcNo - Text (Primary)
TrgConducted - Yes/No
DeptAbbv - Text (Primary)

tbl_Dept:-
DeptAbbv:- Text (Primary)
DeptDetails:- Text

I created a form where i have the following fields in the form:-
1. ProcedureNo - Text
2. TrgConducted - CheckBox
3. MainDept- ComboBox (DeptAbbv data field)
4. Finance – CheckBox (DeptAbbv data field?)
5. Admin – CheckBox (DeptAbbv data field?)
6. Purchasing – CheckBox (DeptAbbv data field?)
7. Facilities – CheckBox (DeptAbbv data field?)
8. MIS – CheckBox (DeptAbbv data field?)

How can I add multiple deptabbv field in a single form?

Pls help.

Thanks.

View 1 Replies View Related

Data From Multiple Tables On Single Form

Oct 14, 2004

I have a tabbed form that contains multiple rows of data just like the following: (it's a service checklist for a store where the user makes choices from the combo boxes: e.g. good, fair, poor, etc)

Paint (txtHrs), Action (cboPaintAction), Condition (cboPaintCondition)
Carpet (txtCarpetHrs), Action (cboCarpetAction), Condition (cboCarpetCondition)
Counter (txtCounterHrs), Action (cboCounterAction), Condition (cboCounterCondition)
etc.

Since the combo boxes describe data that relates only to that element I can't put all the data needed into a single table. There are 40 of these rows so does that mean I have to create 40 tables? If so, how do I add the data to them from the form? Any help with this problem would be greatly appreciated!

View 2 Replies View Related

Mouse Wheel Use On MS Access Single Form?

Feb 28, 2008

Hi all,

Have any of you know a way (maybe in VBA modules) to deactive the use of MOUSE WHEEL while using on the MS Access form since I am creating a form for users to enter hundreds of project data into a project table.

I put certain contrainsts (force the users to enter data on several fields on the form and NOT to leave them blank).

However, the users can use the MOUSE WHEEL while on form to move to the next project WITHOUT entering those required fields?!!

By the way, each project is stored in separate record in its table. The form is a SINGLE FORM, not Continuous form.

Please help. I am looking forward to your reply.
Thanks so much,

View 5 Replies View Related

Datasheet Form How To Change Single Row Color

Jan 14, 2007

Hi
I am running Access 97 and I was wondering if it was possible to change the colour(background or text) of a single record based on an attribute

I was hoping that my subform viewing all of the records (datasheet) would look like this based on Task Des column
eg Condition = red, Awaiting = Blue
(Below is ment to look like an access datasheet view)

Property No | Task Des | MCU/ROL
PN1234/DA1 | Condtion | MCU
PN1235/DA2 | Condtion | ROL
PN1994/DA1 | Awaiting | MCU

I have read you can do this quiet easy in access 2007 but was wondering if possible in 97

Thanks heaps for your help




Thanks

View 2 Replies View Related

Modules & VBA :: Print Single Report From Form

Mar 11, 2015

I'm trying to print a Report specific to the record i'm viewing on my Form. I've created a Report linked to the Table - "SCJobsheet".I've created a button in my Form;

Name; cmdSCJobsheet
Caption: Print Record
OnClick: [Event Procudure]

[code]...

The Report is opening but shows Every record (Not just the one I'm viewing in the Form.

View 12 Replies View Related

Modules & VBA :: How To Open Table In A Single Form

Dec 15, 2014

I am using Access 2003. I would like to be able to open a versatile form that can open any selected table in data sheet form. Currently I am using Command Button with the names of the tables as the trigger. Looking for VBA that would change the form's Record Source to a selected table and add all the fields of that table to the form on open. I figured that I can use the OpenArgs to get the form Record Source to be assigned, but how do I assign all the fields of the table to be dropped into the form?

View 10 Replies View Related

Save Data In Different Tables Using Single Form

Sep 3, 2014

I have created single form and 7 different tables. In my form I have a combobox which have different branch names (Table names are as per combobox values). Now what i want to do is whenever I select a branch name from combo box and click on the save command button the particular record should be saved in particular table.

View 1 Replies View Related

Print Single Record Based Upon Current Form

Apr 6, 2006

I need to Print a single record from a form but using a report layout.

I have created the form and created a report. A button on the Form kciks off the print but I get all records rather than just the one shown on the Form. Whats a really simple way of just selecting the Current record.

Where do I put an instruction saying print only this record:confused:

View 9 Replies View Related

Multiple Lines In Order Form From Single Database

Feb 28, 2005

I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.

View 4 Replies View Related

Printing A Single Records Report From A Continuous Form

Mar 7, 2006

I've been trying to get my head round this one , but i'm just to thick to get it.

I have a continuous form that lists all items at a certain location.
The user selects a record by clicking on the record selector and then clicks on a command button with this code:

stDocName = "DivingInspectionCert"
stLinkCriteria = ("EquipmentID = " & Me!EquipmentID)
DoCmd.OpenForm stDocName, , , stLinkCriteria

The user then enters inspection details in to the "DivingInspectionCert" form which i want to store in a table (DivingCert) which will relate to the item. On completion of this form the user then clicks on a command button with this code:

DoCmd.RunCommand acCmdSaveRecord

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no items to Print", vbInformation, "EquiTrac"
End
Else
DoCmd.OpenReport "DivingInspectionRpt", , , ("EquipmentID = " & Me!EquipmentID)
DoCmd.Close

When i click on this button, the report doesn't print and i get a message "No current record".
The inspection details are not stored in the table.
I did have the forms RecordSource based on the table "DivingCert" but that didn't work and i have just tried a query but it is still not working.

Would be much appreciated if you can help me.
Thank you

View 3 Replies View Related

Forms :: How To Design A Form For All Records In A Single Page

Mar 28, 2014

How to design a form for a table based all records to design in a single form without top to bottom list wise and without scroll bars. Need to form design for all record details are will show on a form as side by side only as horizontal list wise.

For Ex. I have attached the screen shot image as per horizontal wise records continues.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved