Self-Referencing Table Question

Jul 6, 2007

Having (with much help from many of the experts here) overcome the hurdles of junction tables, subform to subform movements, and the seemingly easy design issues of a form, I have now created a self-referencing table. I humbly return to ask for advice from those experienced with these relationships...

I have been led to this in order to load in to my DB a certain class of documents, linking them relationally to their parent documents, while performing the data input for both into the same form. What I have done and what the problem is are as follows:

I created a new field in my form (formDoc), "LinkedExhibit". In the relationship window, I reloaded the Document table and reestablished all prior relationos, then added a second (alias) table Documents1, linking the primary key of this alias to the LinkedExhibits field.

I have the Linked Exh field set up as a combobox linked to DocTitle from the Documents table ONLY for the purpose of saving keystrokes if a duplicate should arise.

Unfortunately, when I enter a new document and then continue entering data through the LinkedExhibit field, and save by moving on to another subform related to these, the Documents table shows all of what I typed, except it shows the Linked Exhibit saved as DocTitle, and the DocTitle that I had typed is gone...

I really am flying blind but believe the alias table is not linked right, or I am using that concept to do something it cannot do...

I am attaching the DB, in Access2007, if someone could lend an eye to it...
I would appreciate that.

View Replies


ADVERTISEMENT

Modules & VBA :: Add Records To A Table Referencing A Table In Another Database - DAO

Jan 16, 2015

I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:

Although this is pretty self-explanatory, here is a key for reference:

Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox

Code:
Private Sub cmdAddMet_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Set DB = CurrentDb

[Code] .....

This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:

Code:
Set DB1 = OpenDatabase("serverotherdb.accdb")
strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'"
Set RS1 = DB1.OpenRecordset(strSQL1)

However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:

Code:
For i = 0 To lstFacilities.ListCount - 1
If lstFacilities.Selected(i) = True Then
RS.AddNew
RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
RS!MEASUREMENT_PERIOD = Me.cboMeasure

[Code] .....

Is it even possible to do this?

View 10 Replies View Related

General :: Combo Box Referencing One Table - Inputting Value From Different Table

Jul 11, 2013

I am trying to use a combo box to select the Company in an input record form for my Transactions. In the Transaction table, each record contains the Company ID, but not the company name (I have a relationship with a Company ID primary key in a separate table that has all the companies information).

I would like to be able to select the proper company in the combo box and have the form save the value as the Company ID number with the rest of the input data in a record (it will then refer to the correct company name in the other table if I query it because of the ID key).

View 3 Replies View Related

Referencing Another Table Via Drop Down

Jan 11, 2007

Hi all,

have used the wizard to take values for a table dropdown field from another table

the table has

surname forename and class all of which reside in another table... ive tried to set up the bound columns so when you select a surname from the drop down the other two fileds also referencing that 6able are automatically filled in with their corresponding values.

so if the ref table had

smith john 1m

by selecting smith in the new tables drop down, the forename and class fields automatically select the corresponding values from the ref table..

im sure this is possible but cant get it to work and cant find anything on the web (although this is probably down to not knoing the right search string)

any ideas?

dubs

View 5 Replies View Related

Cross Referencing Records From A Single Table

Sep 22, 2005

I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.

In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?

How do you do a many-to-many from one table back to that same table?

tblEvent
EventID(AutoNumber) EventTitle(Memo)
1 memo content
2 data
3 more memo content
4 some info
5 more stuff
6 other text

tblEventRelationships
EventParrent EventChild
1 4
1 3
1 5
6 1
3 2

In addition ... how would one set up the integrity to prevent loops from forming?

View 6 Replies View Related

Referencing To Users Collection For A User Table Possible?

Jul 27, 2007

My search was snowballed with lot of results about dealing with split database, seeing who's online, etc. so I'm not sure if that is a good idea.

I want to make a table of Users because my users may need to put their name on some of reports, so I could use CurrentUser() to figure out who's who and put down their proper name after comparing the username via a query.

However, I'm not sure if it's possible for me to link the user table to the user groups of database to ensure that there's no ghosts or users that doesn't have a proper name to be placed on the reports or whatever. More of a validation, really.

Is that doable or maybe there's another way to do that?

Thanks!

View 2 Replies View Related

Question On Table Referencing A Table.

Oct 12, 2006

Howdy all,

I am trying to build a db to track all the different projects I do on my web shop www.mutzigcreations.com (http://www.mutzigcreations.com). One thing I need to do is to be able to track the different materials I make things from. This wouldn't be to bad, but sometimes I use several different materials in one project and for the life of me I can't figure out how to do a table system (or form for that matter) that would let me do this.

I build out of woods, acrylics, and other special materials so I have a table for each of the 3 main types. Then I created tables called Material-1, Material-2, etc..that did table lookups to the 3 main type tables. These tables (material-1, etc..) are then related to my main table called Creations. (is this the correct way to go about it? as I can't seem to get it to work at all)

I would eventually like to set up a form that would have a check box next to Material-1, Material-2, etc...that would enable me to select:

A. if more than one material was used
B. What the materials were.

I didn't want to make several copies of the 3 main tables (wood, acyrilcs, special) as that seemed counter productive since the same info is used for each of the different material-1, material-2 selections.

Can anyone help me figure out what do to, or the best way to do it?

Thank you!
Doug

View 7 Replies View Related

Modules & VBA :: Public Function With Variables Referencing Table Date Fields

May 31, 2014

I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.

Here is the code:

Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")

[Code] ....

I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010

View 5 Replies View Related

Queries :: Select All Dates Since A Given Date Without Referencing A Table? (Access 2007)

Apr 23, 2014

Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?

(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)

I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)

But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)

The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).

It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.

For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).

View 3 Replies View Related

Access 2010 - Main Menu Needs Drop Down Box Referencing A Table For SEARCH

May 6, 2014

I have a main menu "MainMenuF" in my database and I would like the database to be able to be searched by any of the column headings. For example I have a table titled "NewLabReportT" this table has many column headings like "Submitted By" "Reviewed By" "Title" "Abstract" etc...

I would like my users to be able to click a radio button with the same column headers on this main menu and have a box that pops up to say "Enter search parameter" and have that parameter be searched in the table "NewLabReportT" and generate a FORM not a REPORT of all of the listings in the database that have that keyword associated to that certain category searched. That way they can "CLICK" the exact field they are looking for and have it open right up to that form.

View 2 Replies View Related

Referencing

Oct 3, 2007

I know this should be very simple, but I've already tried every possible entry from the site given in the FAQ section of this website (http://www.mvps.org/access/forms/frm0031.htm), but it keeps access keeps asking for a parameter value!!

i have a subform(frmSub2) on a subform(frmSub1) on frmMaster.
I have a combo box (cbo2) on frmSub2 that grabs its value from the value chosen from another combo box (cbo1). Here's what I have tried in the query builder (none work!! why?????):

-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form]![cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form].[cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2]![cbo1]
-[forms]![frmmaster]![frmSub1].[form]![frmSub2].[cbo1]

Please help!!

View 3 Replies View Related

Referencing A Variable

Apr 6, 2008

I want to be able to reference a variable dependant on where I am within a loop. I have myct1, myct2 and myct3 looping then a mycount loop and I want to add a record to a table taking the relevant myct value dependanct on the current mycount value. Any ideas anyone?

Here is my code:

for myct1 = 1 to 3

for myct2 = 1 to 3

for myct3 = 1 to 3

for mycount = 1 to 3

mytable.addnew
myvalue = <if mycount = 1 then myct1 etc>
mytable.update

next mycount

next myct3

next myct2

next myct1

Any help most appreciated.

View 1 Replies View Related

Referencing A ComboBox

Mar 7, 2005

I have code on a subform that is referencing another form that is still open (not the parent form, but the form which's button openned the form that the subform is on). I'm trying to get the value from a combobox that contains the data that will filter the subform in question. See below.
==========================================
Dim strForm As String
Dim strSQL1 As String, strSQL2 As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String

strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON tblMainData.ResponsibleParty = tsubPermissionList.FullName "
strWhere = "WHERE(((tsubPermissionList.UserID) = [Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)))"
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere

If IsNull([Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================

I can't resolve the error:
==========================================
Runtime error '3085':
Undefined function
'[Forms]![frmMainEntry].Form.[cboSelectUser].Column' in expression.
==========================================

Somehow access thinks that the references to the combobox is a function. Can someone help me with this?

Thanks,
PC

View 4 Replies View Related

Referencing A Control

Apr 5, 2005

When clicking on a command button on a form in order to open another form and go to a specified record, i used to use a code similar to the following:

DoCmd.OpenForm "frmName", , , "[FieldName]=" & Me.ControlName

Is it in the same format the above statement if the form to be opened has a tab control ?

Thanks

View 5 Replies View Related

Referencing A Control

Dec 6, 2005

I'm trying to get the value of a textbox on one of my subforms and can't seem to get it properly referenced.

I have a main form Main with a tabcontrol having two pages. One page is Timesheets. Timesheets has a subform called Detail_data.
On the detail_data subform is the text box Time_In that I want to reference. I can get to controls on the timesheet page but not on the detail_data part.

Thanks in advance....

View 1 Replies View Related

Referencing OCX Control

Jul 10, 2007

Hi all

I've distributed a MS 2003 database to another office 1000km away using 2003 as well. The calendar control doesn't seem to be working. I'm assuming that the MSCAL.oxc isn't referenced. Is there a way I can do this programatically without actually having to talk someone through the referencing process over the phone??

Your ideas are appreciated.

View 2 Replies View Related

Referencing A Subform

Oct 28, 2007

When I run the following code in a module -- to change the value of a label in a subform ("Employees Trained List subform" is a subform of "Contractors") I get an error that says it can't find the form (ie. Contractors). Also, if I eliminate "[Contractors]" the error says it can't find "Employees Trained List subform":
Forms![Contractors]![Employees Trained List subform].Form!lblThisYearTrainedDate.Caption = Year(Now()) + 1 & " Trained"
Any ideas on how to fix? Thanks

View 2 Replies View Related

Referencing The Controls On A Subform...

Feb 16, 2007

Hello,

I need some help with a piece of code for a db.

This code checks to see if the last three characters on a ControlSource.Tag property are "Req" and if they are, tests if it's null or = 0 and if it is then it outputs a msg box stating that the control.ControlSource is required. It does it for every control on the main form.

This has worked.

Now, i need to allow for multiple subforms to be located on the main form along with their controls being tested as well. I want to start another loop for each time the conrol on the mainform is a subform.

I have that done so far by using "If TypeOf ctl Is SubForm Then..."

This is the part i need help with. I want to search all of the controls on the subform when it is a subform. I need to search all of the controls on the subform then, and i'm not quite sure of the syntax.

I've tried various somethings like SUBFORMCONTROL.Controls but that hasn't seemed to work...

If someone could help me, that would be wonderful!

View 4 Replies View Related

Expression Referencing Same Query

Oct 24, 2006

Hello,
I am trying to write a simple query that references calculations in the same query - but I keep getting the "enter parameter value" box.

Basically, I have some columns in a query calculating average and sum values for a column. I would then like to multiply some of those columns together in a new column, using an expression like this: =[AvgOfColumn1]*[SumOfColumn2]. Access doesn't want to do this for me. It seems very simple so I'm sure I'm missing something!

Thanks!

View 3 Replies View Related

Crosstab Referencing A Checkbox

Aug 9, 2007

I'm attempting to build a crosstab query that references fields on a form as criteria for my query. I have no problem referencing a combo box as part of my criteria -- the query likes that fine. However, when I attempt to reference a check box (ex. iif(Forms![CheckBox] = true, "A", "B"), I get the following error:

The Microsoft Jet database engine does not recogine '[Forms]![frmMyForm]![Check100]' as a valid field name or expression.

Anyone have any ideas why I can not reference a checkbox but I can other types of fields in my crosstab query?

Thanks in advance!

View 3 Replies View Related

Referencing A Sub Form Within A Tab Control

Apr 11, 2005

Hey guys,

a quick question.

I have a form called "FormA"
within that form i have a tab control
and one of the pages is called "Page1"
inside that page1 i have a subform named "subform1"
and finally inside subform1 i have a field named "field"

what would be the correct syntax to reference the value of the field???

been messing with this for awhile and am getting a headace, was hoping someone can help

thanks alot

Ricky

View 2 Replies View Related

Referencing A Global Variable

May 18, 2005

I am trying to use a global variable in a dlookup but get an error when i try and run the form

Here is the code:

VUserID is my Global Variable.

Private Sub Form_Open(Cancel As Integer)

Dim UsersName As String

Me.WelcomeBox = UsersName

UsersName = DLookup("[Name]", "tbl Password", "[username] = vUserID")

End Sub

Any Ideas

View 1 Replies View Related

Syntax For Referencing A Subform

Oct 24, 2005

Newbie question: I have a datasheet view subform (called "UpdateProjectsSubform") in an "empty" form. I set it up this way because I was unable to get the Switchboard to open the subform in datasheet view.

I would like to have a command button to set filters in the subform. As I cannot have a button in datasheet view, I think my best option is to place a button on the master form that performs the command on the subform. I have tried every which way, but can't seem to get the correct syntax to reference the subform. I have searched this forum as well as other references with no success. Any ideas?

Thanks!
Nancy

View 6 Replies View Related

Referencing Data In Combo Box

Feb 3, 2006

Basically MY problem is that i set the control id to that of a primary key so that when the combo box data is changed it puts the data in the table. However my problem is that the data it displays is a price and im trying to calculate a total. Therefore in VBA im trying to find out how to refer to the data displayed in the combo box but isnt the control source.

Thank you in advance
Any help would be much appreciated

View 1 Replies View Related

Referencing Many Tables In One Form

May 8, 2006

Hi all,

Firstly I am trying to retrieve data from three tables in a format like this

Table Data Fields Descirption

Table 1 A,B PC Details
Table 2 B,C Software/PC
Table 3 C,1,2 Software Details

Where the B's are linked as are the C's and these both form primary keys in their respective tables.

My form has on sub form in it. The main for refers to Table 1, displaying Fields A and B. My subform is linked through child/master fields by B and is used to edit which software titles are installed on each PC. However, as C, the primary key for the software, is a code, it is not obvious what it refers to exactly. Therefore I use a combo box who's row source is query containing the software title (1) and the column widths precceding the software title are set to 0 so that software title (1) is visible in the field in the subform. Hence, although you change the title name on the form, the actual change is made in Table 2 altering the Software Code (C). This is fine, except that there is more than one version (2) of some of the software titles. The versions are shown in the drop down list for the software title combo as an extra colum, so you know you are choosing the correct one. But i also want a seperate field which actually shows the version chosen. I cannot link a normal text box to the same query as it doesn't have the relevant properties, but I can do it with a combo as I am effectively doing the same as for the software title.

This is all very well, I could just lock the combo box and be done with it, but it doesn't look very professional and is a bit confusing to the user. Is there away that I can get around this problem?

Cheers,

Matt

View 7 Replies View Related

Referencing A Control On A Subform

Sep 2, 2004

I'm trying to check if a control on the subform is null. I've the following line of code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Form!FrmOrderInfoMain!SubFrmOrderInfo.ItemC ode) Then
Msgbox "Empty ItemCode"
End If
End Sub

..whereby FrmOrderInfoMain is the parent form and SubFrmOrderInfo is the subform. I get an error for this code as Im not sure what is the correct code to use. Thanks for the help.


Swee

View 7 Replies View Related







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