Error with opening and printing report: insert parameter value

[LadyL]

I have one small problem with my database.
I want to print out a report from a form with a button with this code:
Code:
DoCmd.OpenReport "Labelsplits", acNormal, , "([Splitsvondst].[vondstnummer] = " & Me.vondstnummer & ") AND ([Splitsvondst].[categorie] = " & Me.categorie & ")"
However it asks me for a parameter value for both splitsvondst.vondstnummer and splitsvondst.categorie. The form is called Splitsvondst. Should it not just take the value from its own form? Does anyone know why this can happen?



Thanks again in advance.

[June7]

So vondstnummer and categorie compose a compound index in Splitsvondst but they are not used as compound foreign key? The vondstnummer value is FK in 2 tables and categorie value is FK in 1 table. This is odd structure. It allows duplicate vondstnummer and duplicate categorie but not duplicate combinations. This means if there are duplicate vondstnummer values, the FK links will not know which record in Splitsvondst to associate with so will associate with all that have the same vondstnummer. Why is vondstnummer not an autonumber field and designated as PK which is then saved in other tables as FK?

[LadyL]

This is the case because I need to have duplicate Vondstnummer values. The finds will be split with the same Vondstnummer, but different categories (categorie). In order to be able to print cards with the same findnumber (Vondstnummer) but different categories (categorie). This is what the button Vondst Splitsen does in the Vondsten form. It is not an autonumber field because I need to be able to put them in by hand in the order that I get them delivered to me (bags of finds with the number card in them that I need to input in the database. Does this make any sense?
So it needs to select the data in the report that has the same findnumber (vondstnummer) and category (categorie) as the current record in the form.

My other report button opens and prints the report fine (Vondstkaartje, in the form Vondsten), but that only needs to select the Vondstnummer, not also the categorie.

[June7]

Okay, but the issue with table links still applies.

I am working with previous version of your db which does not have this report and code so not sure if the following suggestions are appropriate.

Try removing the table reference prefix. Make sure fields are in the report RecordSource.

DoCmd.OpenReport "Labelsplits", acNormal, , "([vondstnummer] = " & Me.vondstnummer & ") AND ([categorie] = " & Me.categorie & ")"

[LadyL]

If I do this I get a data type mismatch in criteria expression error.

[June7]

Your API code in Module1 is erroring because I have 64-bit computer. I delete the entire module to eliminate the annoyance. Then debugger errors on form Zoekformulier - "Missing method or data member" on Me.werkput. And form Sporen has an 'Invalid ReDim' error. I delete these two procedures also.

Error is because Categorie in Vontscategorieen is Text, not Number type. The combobox on form has a number value - the ID field. Categorie in Splitsvondst is number type. You also have Lookup set on this field in table. I NEVER do lookups in tables. http://access.mvps.org/access/lookupfields.htm

[LadyL]

I know, but I have used lookups for like forever. Do you have any suggestions as to how I can make it work? Thanks again.

[June7]

Decide to save either the ID or the descriptive text and get the Categorie fields in all tables the same data type. Then adjust the combobox RowSource and code if needed.

[LadyL]

I have tried removing the ID field from Vonstcategorieën, but I now get a whole lot of new errors which I do not know how to fix. Once I want to open my split finds I get this error:

Private Sub Knop78_Click()

Dim lngSelectie As Long 'onthoudt welk record momenteel geselecteerd is
Dim lngCategorie As Long 'onthoudt de geselecteerde materiaal categorie


'Bepaal het huidige geselecteerde record uit het subform
lngSelectie = Me.Subformulier_Splitsvondst.Form.CurrentRecord


If lngSelectie > 0 Then 'als er een selectie is
  With Me.Subformulier_Splitsvondst.Form.RecordsetClone 'vind de geselecteerde materiaalcategorie
    .MoveFirst 'zoek vanaf het begin
    .Move lngSelectie - 1 'we zitten nu op 1, dus ga selectie - 1 vooruit
    lngCategorie = .Fields(1)  Type mismatch
    .Close 'sluit de tabel weer netjes
  End With
  
  If lngCategorie > 0 Then 'als de materiaalcategorie gevonden kon worden
    DoCmd.OpenForm "Splitsvondst", acNormal, , "(vondstnummer = " & CStr(Me.vondstnummer) & ") AND (categorie = " & CStr(lngCategorie) & ")" 'open het formulier
  End If
End If
End Sub

I only need it to print one tiny little report and now I need to rearrange my whole database to make it work? In my old database I had the exact same code and it did work. Is there any other way around this?

[June7]

What changes did you make in tables? Did you make categorie fields all text or all number?

lngCategorie is declared as Long. If the field is now text then the variable cannot hold the value.

If you keep everything as it was and the ID in the combobox RowSource, then the open report code needs to reference the combobox column that has the descriptive value, not the ID.

"[vondstnummer] = " & Me.vondstnummer & " AND [categorie] = '" & Me.categorie.Column(1) & "'"

[LadyL]

That works, OMG thank you, I was kinda lost!

[June7]

You may still encounter issues because of categorie being different field type in various tables.