Navigation pick list problems

[Trestkon]

Ahoy!

I've been trying to create a navigation pick list from a form using this handy FAQ:

http://www.tek-tips.com/faqs.cfm?fid=3765

However, I'm running into a bit of a problem. The picklist works, but only until I close and re-open the form, at which point it gets NoMatch. So, if I have the form open and type in 5 records, I can use the picklist perfectly to navigate between them. After closing and re-opening the form none of the previously entered records can be navigated to (although they still show up in the pick list). I'm able to enter *new* records which I can then naviage between, until I re-open the form, at which point they will also cease to work.

I'm using a 3 column list box with 1 unique identifer column (hidden) and two descriptive columns.

Any help would be very, very much appreciated

 

[TheAceMan1]

How are ya Trestkon . . .

[blue]After closing and re-opening the form none of the previously entered records can be navigated to ...[/blue]

You need to use a [blue]bound form![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

Thanks AceMan, I'll look into that!

 

[Trestkon]

Hmm, okay, I'm not entirely sure how that would work. I tried binding it to the unique identifier column, but then when you click it tells you that the "control cannot be edited", which is true, because it's an autonumbered entry.

 

[Trestkon]

Here's the code I'm using:

Private Sub SiteNumNavBox_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "SiteID=" & SiteNumNavBox.Column(0) & ""

If rst.NoMatch Then
MsgBox "Could not find the record for some random reason, better consult Lawrence. Or throw your computer out the window, which is a rather nice solution for stress relief.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

 

[TheAceMan1]

Trestkon . . .

Were not on the same page here. Post back the [blue]RecordSource[/blue] of the form and the [blue]RowSource[/blue] of SiteNumNavBox . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

The form RecordSource is thus:

SELECT tblProgramInfo.SiteID, tblProgramInfo.Site_Number, tblProgramInfo.Sequencer, tblProgramInfo.CapWorkOrder, tblProgramInfo.[CapProgram Year], tblProgramInfo.[CapDevelopment Year], tblProgramInfo.CapStatus;

and the RowSource is:

SELECT tblProgramInfo.SiteID, tblProgramInfo.Site_Number, tblProgramInfo.Sequencer FROM tblProgramInfo ORDER BY tblProgramInfo.Site_Number;

 

[TheAceMan1]

Trestkon . . .

There's no From clause ([blue]FROM tablename[/blue]) in the SQL for the forms recordsource!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

Whoops, pasting error. It should be this:

SELECT tblProgramInfo.SiteID, tblProgramInfo.Site_Number, tblProgramInfo.Sequencer, tblProgramInfo.CapWorkOrder, tblProgramInfo.[CapProgram Year], tblProgramInfo.[CapDevelopment Year], tblCapProgramBudget.[Cap09/10], tblCapProgramBudget.[Cap10/11] FROM tblProgramInfo INNER JOIN tblCapProgramBudget ON tblProgramInfo.SiteID=tblCapProgramBudget.CapSiteID;

 

[TheAceMan1]

Trestkon . . .

The SQL sources you provided are just fine for the cause and have nothing to do with the problem. Indications are that some intervening code is taking hold on opening of the form ... otherwise nothing makes sense. To speed things up and if their are no proprietary issues, up load a scaled down model of the db to [link 4shared.com]4Shared[/url] and post the link. Convert the db to access 2000 before sending.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

Thanks AceMan. I'll strip some stuff out and post it early this week. Trying to avoid work on the weekend

 

[TheAceMan1]

Trestkon . . .

BTW: [blue]4Shared[/blue] is free!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

Apologies for the rather tremendous delay! I took some holidays and then was starting up my classes for my last year of Uni (only working part time, now).

I've attached the database, which should illustrate the problem outlined in my original post. Mainly:

"The picklist [on the ProgramInfo form] works, but only until I close and re-open the form, at which point it gets NoMatch. So, if I have the form open and type in 5 records, I can use the picklist perfectly to navigate between them. After closing and re-opening the form none of the previously entered records can be navigated to (although they still show up in the pick list). I'm able to enter *new* records which I can then naviage between, until I re-open the form, at which point they will also cease to work."

Any help would be most apprciated! Oh, and I realize that all of my data is probably not normalized properly...it's a bit of a work in progress

 

[TheAceMan1]

Trestkon . . .

The link you provided fails! Try 4Shared

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Trestkon]

Hmm, seems the forum attachment thing tacked on a bit to the beginning of the URL. This should work (just put it up on my own webspace):

http://www.offtopicproductions.com/Lawrence/public/LawrenceDB.zip

 

[formerTexan]

Treskton,

Seems you need to include SiteID in the form's recordsource. I believe there was an earlier comment about normalization and from a cursory glance I second that.

Cheers, Bill

 

[formerTexan]

Trestkon,

Sorry, but please disregard my prior comment. The form's recordsource (SQL statement) was truncated at my end when I first had a look.

However I did notice that the form's recordsource as you written it doesn't return any records. Hence no record to bookmark. This is a consequence of the table joins (intentional?). Perhaps you want to use outer joins rather than inner joins.

Cheers, Bill

 

[Trestkon]

You're bang on with the truncation! It looks like I've reached the limit of the record source length, and actually can't include that many entries

I'm not entirely sure how to normalize this data and fix that problem. Any suggestions? Normalization has been the hardest thing for me to wrap my head around, and it's been a bit frustrating seeing as I hvae nobody around the office to talk to that knows anything about this.

Luckily I have you guys!

 

[Trestkon]

Hmm, correction. It seems that when I attempt to include fields from *more than* 2 tables, it truncates the Record Source and that of course screws up the whole thing. If I only include records from 2 tables it works just fine. This is all using the wizard to build it, of course.

 

[formerTexan]

Save the form's recordsource as a named query to dodge the string length limitation and resolve the truncation issue. Then use the named query as the recordsource.

As for my comment on normalization - just a gut feeling from looking at the field names in tblCapProgramBudget and tblPresProgramBudget. Don't know what you are doing with these fields, so just a guess.

Cheers, Bill