Organizing multiple tables into 1 Report

[bobbobruns12]

Hey I have four tables that I am looking to collect into one report. I created a query and have combined all of them but now have to write the code in order to call up specific records based on id numbers. My original code to do this before with just 2 tables is this:

Private Sub TransDatacmd_Click()
On Error GoTo Err_Transdatacmd_Click

Dim stDocName As String
Dim stLinkCriteria As String

TransDatachk.Value = True
stDocName = "TransDataEntryfrm"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'saves record
strAddFilter = " ID= " & Chr(34) & Me.CompleteIDtxt.Value & Chr(34)
DoCmd.OpenForm stDocName, , , strAddFilter, , , Me.CompleteIDtxt
Exit_TransDatacmd_Click:
Exit Sub

Err_Transdatacmd_Click:
MsgBox Err.Description
Resume Exit_TransDatacmd_Click
End Sub

I have been generally putting this code (or altered versions) into the command buttons and would like to write code to call up all the different data from all the tables based on the ID number. However I have different IDs connecting 2 of the forms. This seems complicated, to me anyway, and if anyone has any ideas on how to write code to accomplish this I would greatly appreciate it. Thanks

Bob

 

[lameid]

Assuming they are all joined together you shouldn't have any real problems. This is true unless of course you have an unusual situation with the same ID's used differently in more than one table.

Anyways, post your SQL from your query here and we may be better able to understand what you mean. Also, a little bit of information about the tables may prove helpful like what the primary key is and what relationships are setup between them.

 

[bobbobruns12]

Sorry to get back so late I was off yesterday

Here is the SQL for my query:

SELECT RealEstatetbl.PropertyName, TransactionDatatbl.TransactionType, TransactionDatatbl.Buyer, TransactionDatatbl.Seller, TransactionDatatbl.Broker, TransactionDatatbl.Lender, TransactionDatatbl.Developer, TransactionDatatbl.MainPrice, TransactionDatatbl.TransactionSpecs, TransactionDatatbl.Date, Contacttbl.[Buyer Contact], Contacttbl.[Seller Contact], Contacttbl.[Lender Contact], Contacttbl.[Developer Contact], AddPlayerstbl.Profession, AddPlayerstbl.[Company Name], AddPlayerstbl.[Contact Info], Contacttbl.ContactID, AddPlayerstbl.APID, TransactionDatatbl.ID, Contacttbl.[Broker Contact]
FROM ((RealEstatetbl INNER JOIN TransactionDatatbl ON RealEstatetbl.CompleteID = TransactionDatatbl.ID) INNER JOIN AddPlayerstbl ON TransactionDatatbl.TransID = AddPlayerstbl.APTransID) INNER JOIN Contacttbl ON TransactionDatatbl.TransID = Contacttbl.ContactTransID;

I have the tables set up so that the Transaction Data Table is set up to the Asset Data Table, connected through an ID number. The Contacts table and the Additional Players table are connected to the transaction data table by a different unique id number however for reference purposes I put the ID of the Original Table In this table, however the primary key is the transID.

If that's Confusing...

RealEstatetbl -> TransactionDatatbl Through ID
TransactionDatatbl -> Contacttbl
&-> AdditionalPlayers
Trough TransID

Thanks if you have any ideas let me know

 

[lameid]

Everything seems in order. You use an ID field as a filter and there is one ID field in your SQL...

The only thing I see looking at this in detail is that you have an openargs value when opening your form. Is the OpenArgs value used on the form's load or open events?

 

[bobbobruns12]

It's in the Form Current Event

 

[lameid]

My only guess is that the current event is messing things up...

Do you get an error message anywhere?

Perhaps the current event code might help me see something.

I noticed you are opening a form in code but talk about a report... The command is not mismatched with the object is it? (Nothing is jumping out at me so I am grasping at straws).

 

[bobbobruns12]

Oh I'm sorry I confused you. No the above code works fine. I was just using that as an example of the code I've generally been using. Ok here is the code I developed for a command button on the transaction data form to create a report with it registering the same ID and transid to bring together all the right data from the correctly id'd records and place them in this report

Private Sub TransReportcmd_Click()
On Error GoTo Err_TransReportcmd_Click

Dim stDocName As String

stDocName = "TransactionDataRpt"
strTransFilter = "ID =" & Chr(34) & "APID =" & Chr(34) & "ContactID =" & Chr(34) & Me.TransDataIDtxt & Chr(34)
DoCmd.OpenReport stDocName, , , strTransFilter


Exit_TransReportcmd_Click:
Exit Sub

Err_TransReportcmd_Click:
MsgBox Err.Description
Resume Exit_TransReportcmd_Click

End Sub

I know this is probably really wrong but because there's so much involved I can't seem to find a way to code it correctly. When I press the cmd it says "Syntax error(missing operator) in query expression '(ID="APID="ContactID="'")'

Sorry I'm up to my eyeballs in code. Hope that clears things up

 

[lameid]

In your criteria, you are not combining them together logically with AND or OR.

strTransFilter = "ID =" & Chr(34) & "APID =" & Chr(34) & "ContactID =" & Chr(34) & Me.TransDataIDtxt & Chr(34)

Secondly you are only putting in a value for ContactID.

What should ID and APID be equal too?


strTransFilter = "ID =" & Me.CompleteIDtxt & " AND APID =" & Me.txtAPID & " AND ContactID =" & Me.TransDataIDtxt

Since you named the fields ID I assumed they should be numeric... You can substitue the fields with whatever controls are appropriate. If it really is text you can concatenate the Chr(34) back end or you can embed the quotes by doubling them up...

strTransFilter = "ID =""" & Me.CompleteIDtxt & """ AND APID =""" & Me.txtAPID & """ AND ContactID =""" & Me.TransDataIDtxt & """"

 

[bobbobruns12]

ok the four tables are

RealEstatetbl
TransactionDatatbl
Contacttbl
AddPlayerstbl

The system is set up that the first form is a search form. you click the search it gives you a few results and displays them with a link to click and see more. you click on that and you get the ResultDetailsfrm. You then click a cmd button to see TransactionDatafrm which contains the cmd button to create this report. I have 2 IDs, Lets call them CompID and TransID. I need this report to filter so that the CompID from the ResultDetailsfrm = the CompID on the TransactionDatafrm, then the ContactTransID & the APTransID need to be = to the TransID on the TransactionDatafrm, so that all the information produced for the report is specific to that exact record

 

[lameid]

You joined on ApTransID and Trans ID in your query so they already have to match. All you need is criteria for the two fields...

strTransFilter = "TransactionDatatbl.TransID = " & Me!ContactTransID & " AND CompID = " & Me!CompID

Your SQL statement doesn't have a CompID so you may have to adjust it to the Fully Qualified Name. See my last post if these are really text fields.