Linking tables to forms and forms to report

[wjcormier]

Hopefully I won't confuse anyone with my question...

I have a table that contains a field for Owners. I built a form with a combo box that is supposed to list all the owners from the table. I got it to list them but there is also the number 1 that is listed. I'm not sure why. I also have a button that is supposed to send the owner selected on this form to a certain text box on the report. For example, there is a text box on the report labeled "TO:" and the owner selected on the form is supposed to fill that text box in the report.

Then I made the report's "Row Source" link to the form. But when I click the button on the form to bring up the report, the text box is populated by the number 1.

I'm sure I'm making a big mess of this. Especially since I have VERY limited experience with access. Can someone please explain how I can link a table to a form and then link that same form to a report? ANY help would be appreciated!

 

[Mr. B]

Ok. let's take one thing at a time.

Let's get the combo box working as you want. It sounds like you have populated your combo box with the records form the table and have selected the RecordID field and perhaps the Owner name as fields to have in the combo box. If this is the case, first, with your form in design view, select the combo box and display the properties for the combo box. Display the Format tab of the properties sheet. Locate the "Column Widths" property and type: 0 in this property. This will tell Access that you want to set the width of the first column of data in your combo box to a zero width. This will hide the first column. If you view your form in form view and try your combo box you should now see just the Owner name. If you have not change the default value of the Bound Column property on the Data tab then your combo box will still hold and/or return the record ID value which is what you want, but it will just display the Owner information.

You can make the "Row Source" of your report to be the table where your data is stored or a query that returns the records you need from the sources you need. You do not need to apply any criteria to this row source as you will have the report to move to the correct record when it is opened. See below.

Assuming that you want to preview the report before it is actually printed, to open your report to the record displayed on the form, you would need to add VBA code to the On Click event of the button like the following air code:

IF Me.NameOfYourComboBox> 0 then
     'declare a variable to hold the current record ID value 
     Dim lngRecordID as Long
     'assign the record ID value to the variable
     lngRecordID = Me.NameOfYourComboBox
     'open the report, using the "Where" parameter of the OpenReport method
     DoCmd.OpenReport "YourReportName", acViewPreview, , "RecordID = " & lngRecordID & "

End If

The "acViewPreview" in the OpenReport method requires that the report be visible for review. Check the "OpenReport" method in the Help file for more info.

Be sure to change the "NameOfYourComboBox" to the actual name of your combo box and the "YourReportName" to the name of your report

 

[wjcormier]

Thank you for your reply!

Firstly, just for the sake of clarity, I'll try to list out EXACTLY the steps I took to get where I am currently. I was following your advice and my combo box went blank.

In dealing with the table:

1.I have a table named "Assets" that has a field containing the the "Owners".

Now the Form:
1. I have a form named "Owner". I explained its purpose in the previous post.
2. I have a combo box where under its properties, I have the control source BLANK.
3. Under row source, I clicked the drop down option (not the option with (...) on its right) and selected "Assets".
4. Row source type is: Table/Query.
5. Bound column: 2
6. Changed column width to 0 and now the combo box shows NOTHING in form view.

Now for the Report:
1.Report name is "Assets by Stock Number"
2.I have the "TO:" that needs to be populated by the "Owners" form as a text box.
3.For its control source, I clicked on the option to the right of the arrow drop down option, and chose the following:
a. Expression Elements: Double clicked on the database I am using. Then chose
"Forms" ----> All Forms ---> Form named "Owners".
b. Expression Categories: double clicked "<Form>"
c. Expression Values: double clicked "<Value>"
d. In the blank square above it reads: Forms![Owners]<<Expr>>Forms![Owners]
which gives me an error message reading: The expression you entered
contains invalid syntax. You may have entered an operand without an
operator."


I hope this better explains what I did. Again, thank you for your help!

 

[Mr. B]

Let's try to get the combo box working first.

Try setting the Column Count property of your combo box to 2 and see if that makes the list visible.

If you only have 1 in the column count property then you will not see anything because you are telling that column to be a zero width.

I have no idea what the structure of your database is so it is really hard to try to help you with a report but if you can create a query that will return all of the records you want displayed on the report and include in that query the record ID for the owner then you can open your report using the code that I provided.

If you can attach a copy of your database I can take a quick look and see what is going on.

 

[wjcormier]

Once again, thank you for your help.

As for the problem of why the number 1 appears, come to find out that the 1 appears because it is actually in their table. It happened to be a part of the information that they gave me. So that solves that.

But the next problem I have is trying to get the sum of the "Quantity". I have a text box in the "page header" to display the total quantity calculation. For the "control source" I typed in =NullToZero(Sum([QTY AUTH])). When I run the report I get an Error message.

Also, when I came in today to work on it, a message popped up asking me to enter a Parameter value. It is labeled NullToZero and underneath it there is a blank space. Now I am thinking it is there because I was told to prevent my calculated fields from being empty, I needed to convert "Nulls" to "Zero" in the "Module Window" by using a NullToZero function. So this is the exact code I used:

Functiion NullToZero (anyValue as Variant) As Variant
If IsNull (anyValue) Then
NullToZero = 0
Else
NullToZero = anyValue
End If
End Function

When I went to try to delete the code, I couldn't locate it. I'm not too sure if the code is necessary.