Combo box help plz

[wizz_darlo]

Hi,
I have one combo box on a form which I have set to lookup values on the form and display the details. I have the combo box to read the values from a table tblCommReport. The fields which I need to lookup are system number and report number.
The problem comes up because there can be more than one report for each system. So if I choose 3243 01 then it pulls up report number 01 for system 3243. However if i then click on 3243 02 it does nothing. The form doesn't show the next report.

Here is the code:

Private Sub Combo95_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System Number] = '" & Me![Combo95] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I created this using the wizard as I don't really have any skill with vB.

I tried to add Me.Requery or Me.Refresh in that code but it didn't seem to work.
I understand that the bound column of my combo box is System number, but is there no way to allow it to recognise that there is more than one entry for each system?
Also if somebody suggests that I use cascading combo boxes and to look in the example databases then I'm sorry but I have looked and I'm too much of a novice to adapt these to suit my needs. I'm obviously not sure but I seem to think the solution may not be that complicated.

I'd love it if somebody could help.

 

[ColinEssex]

If there is more than one report for each number, how do you know which is the correct report to display?

Col

 

[wizz_darlo]

The user does not necessarily know which report they will need to look at however the report number is unique for each system, like i said 3243 01, 3243 02, 3243 03.
The problem arises because they need to be able to view the other reports if they want to. So if they click on 3243 01 and realise this is not the one they want, then they need to be able to click on 3243 02 and the form refresh to show the new record.

 

[ColinEssex]

2 things

1) try it without the IF Not

Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System Number] = '" & Me![Combo95] & "'"
    Me.Bookmark = rs.Bookmark

2) put an End If as the last line

' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System Number] = '" & Me![Combo95] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

I tested the first way and it works fine.

Try not to have spaces in your control names, this can lead to confusion later on

Col

Col

 

[wizz_darlo]

1) try it without the IF Not

Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System Number] = '" & Me![Combo95] & "'"
    Me.Bookmark = rs.Bookmark

2) put an End If as the last line

' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System Number] = '" & Me![Combo95] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

I tested the first way and it works fine.

Thanks v. much for your reply Colin. Appreciate you trying to help me out! Unfortunately I'm still not having much luck.
If I take out the If statement then nothing at all happens. I select a record from the combo box but that record isn't displayed on the form.
So I tried the second way and it jumps to the debugger and says "End If without block If"
I tried something different and set the report number as the first column. This was slightly more successful because you could flick through each report for a single system however you couldn't view a different system.
So for example if i was on 3243 01, i can view 3243 02 and 3243 03 but I can't switch over to 4311 01.
I'm assuming this is because it is recognising the number 01 in Report Number as a single field without it linking it to the system number?!

Any more suggestions?!?!
I'm still tinkering!

 

[ColinEssex]

Then its got to be your numbering system you're using.

You should have an ID (Autonumber) field as your first field in the table.
I would include that as the first field in the combo and set the length to zero. Have your system number as normal length. That way the user will see your system number but it'll search on the ID (Autonumber) field and won't confuse itself with your numbering system. Don't forget to change the code to reflect the new search field.

Col

 

[wizz_darlo]

I'm not sure how to use an AutoNumber ID system. The numbering system I'm using is important as it is set to Ministry of Defence standards. The system number relates to a ship's system and the report number relates to part 01, 02 or 03 etc of that system.

Will much of this have to change if i use Autonumber?!

 

[wizz_darlo]

Colin,

I figured out what I think you mean...!
My numbering system didn't need to change, but the way I referenced the numbers did in my form. I added an autonumber field on my table and now the combo box looks up the autonumber field and displays the relevant system and report. I made the first column on the combo box 0cm though so you can't see the autonumber.

Well it works now, is this what you meant for me to do?!?!

Thanks

 

[ColinEssex]

I'm not sure how to use an AutoNumber ID system. The numbering system I'm using is important as it is set to Ministry of Defence standards. The system number relates to a ship's system and the report number relates to part 01, 02 or 03 etc of that system.

Will much of this have to change if i use Autonumber?!

None of it will change your numbering system. Its just a field with the property set to AutoNumber. Its an internal reference number you can use to succesfully link tables to each other and to use as a unique identifier.

You use the Autonumber ID to link tables in the relationships window.

Col

 

[wizz_darlo]

Seeing as you're being very helpful Colin I was hoping you might be able to suggest why something else is happening now...
A while ago I spent a long time trying to figure out how to code a button to show an Access Report based on a single record. The wizard's button only outputted all the records in to the report.
I managed to figure this out and also to code a button to email the report as a snapshot to set email addresses.
However the buttons always outputted each record for each individual system, so going back to my example, if 3243 had 01, 02 and 03, then it was outputting all three in to the report when ideally I only wanted one.
So I thought the autonumber gave an ideal solution to the problem. Just change the criteria for the report output to AutoNumber field.

The code I already had looks like this

Private Sub Command63_Click()

    Dim strReportName As String
    Dim StrCriteria As String

    If NewRecord Then
        MsgBox "This record contains no data." _
             , vbInformation, "Invalid Action"
        Exit Sub
    Else
        strReportName = "comreport1"
        StrCriteria = "[System Number]='" & Me![System Number] & "'"


        DoCmd.OpenReport strReportName, acPreview, , StrCriteria

    End If
End Sub

I changed the StrCriteria to = "[AutoNumber]='" & Me![AutoNumber] & "'" but this gave me the following error...

Run-time error 3464
Data type mismatch in criteria expression

Any ideas?!
Thanks

 

[ColinEssex]

You're defining strCriteria as a string and trying to attach a number to it.

Change the SrtCriteria definition.

Col

 

[ColinEssex]

Also, you shouldn't name a field "AutoNumber" call it IDNo or something. "AutoNumber is a reserved word in Access. (like Name or Date or Month)

Col

 

[wizz_darlo]

I guessed that I should change it from being defined as a string to an integer?!
The only problem is my skills at re-coding leave something to be desired!

I thought it would be as simple as this...

Private Sub Command63_Click()

    Dim strReportName As String
    Dim strCriteria As Integer

    If NewRecord Then
        MsgBox "This record contains no data." _
             , vbInformation, "Invalid Action"
        Exit Sub
    Else
        strReportName = "comreport1"
        strCriteria = "[AutoNumber]='" & Me![AutoNumber] & "'"


        DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End If
End Sub

but it's not
Sorry I'm being such a pain, but I am slowly learning!!

 

[ColinEssex]

Presumably your report is based on a query. Put the criteria in there to give you the record you need.

Col

 

[wizz_darlo]

Any chance you could be slightly more explicit in detail. I'm sorry but I do admit I'm crap with this whole thing.
I think my report is based on the same table that my form is based on.

 

[ColinEssex]

All reports and forms should be based on queries, not direct from the table. This allows for greater flexibility and for reports you choose the fields you need as output. In the query grid you enter the criteria to get what data you need for the report - like a date range or specific records etc.

If you need a report to focus on one [system number] then you can have a small form pop up to get the user to type in the relevant number, you then reference the small forms field in the query criteria like this

Forms!YourFormName!YourFieldName

or you can get a little pop up to occur automatically by putting this in the [system number] field query criteria

 [Enter system number for report]

I suggest you practice making a query and trying it out.

Col

 

[wizz_darlo]

It was all working cool with the button I had on the form to view the report just with one slight glitch. Is there no way that this can be easily adapted to accommodate the addition of the Auto Number?
The end user of this database will know a lot less about access than I do, so a button with a picture of a report on it is a lot easier than pop up forms. :S

 

[ColinEssex]

How will you (or the user) know what the Autonumber is?

Surely the reference for the user is your System Number

Col

 

[wizz_darlo]

I don't want them to know what the AutoNumber is, like you said, the Autonumber is there simply as a reference. It has done it's job perfectly in allowing me to call up each record for each system number.
I just wondered if it could now be used as the unique identifier to then call up a report. I'm confused as to why it won't because the button I have was all too happy to open a report for a specified system number, so why wont it open a report for the autonumber which now relates to that system number!!??

 

[ColinEssex]

I don't want them to know what the AutoNumber is, like you said, the Autonumber is there simply as a reference.

Exactly!

Do as I suggested earlier. The user will have to identify the record to print in some way.

For example, they may have the complete record form in front of them on the screen.

There's a button on that form to print it.

A hidden field on the form contains the AutoNumber.
The query references that hidden field (as I described) and pulls the record in the query which drives the report and prints it.

All the print button does is opens the report, the query driving the report will run and pull the correct record for printing.

Col