Web Development

Ask something
Question Locked

Visual Basic/ MS Access Form

0 helpful
17 replies

by amyohare

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

On a form with an ADD record button. Once clicked the button commands an assocaited listbox to pull in data. That listbox has defult lookupdb that pulls in the first of a record in a number column from an associated table. This dblookup works fine, my problem though, is that it pulls in the first record. I need it to pull in the LAST record, plus 1. For example, once I hit the add button, the listbox shows 1. I need it to show the last number of 10 plus 1 (so that would be 11). How do i do this?

I've tried using a descending query, and I've tried using the acLast+1 in many different parts of the

17 replies

by scott_heath

A control can either be populated one way or the other. YOu can't link a query then add an item manually. So either parse the query results and add them to the control one at a time or add the new item to the table that populates the list box then refresh it.

Regards,
Scott

http://scottisageek.spaces.live.com

by amyohare

Show me what the visual basic code might look like for your first suggestion...

I need it to populate the last+1 record number automatically

by amyohare

=DLookUp("Code Number","CatalogPageRequests"," acLast")

anyone...?

by scott_heath

So you want one record, whatever the last record was, plus one. Or do you want all the revords, plus the new one?

Is there a primary key column?

by amyohare

That's exactly right, one new with the field showing not the most recent (last record) but the most recent record plus 1.

There is no primary key column, that column I replaced with the numbers I am working with, i.e., 1 - 10 (and so forth)

Do you need all of the code?
Thanks!

by scott_heath

As long as there is no private info, yes. It would really help.

by amyohare

Option Compare Database

Private Sub Code_Number_AfterUpdate()

End Sub

Private Sub Code_Number_BeforeUpdate(Cancel As Integer)

End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Code Number] = " & Str(Nz(Me![Combo12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

DoCmd.GoToRecord , , acNewRec

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

DoCmd.GoToRecord , , acNext

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub Text36_BeforeUpdate(Cancel As Integer)

End Sub
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

DoCmd.GoToRecord , , acLast + 1

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub

by scott_heath

Most of your buttons just say 'CommandXX'. Can you provide me a little flow information. If you have a SkyDrive or Dropbox it would be cool if I could get the DB. It's just easier to play with, since I don't have the rest of your database structure.

by scott_heath

So you have "Code Number1", this is the drop down box filled with a query. Then you have "text36". When the form loads it fills the "Code Number1", but it does not show the most recent (hightest valued) number there or in text36, but it does seem to show record data in the form.

So do you want it to automatically show the newest (highest value) in both places on load? Then when you click new it updates text36 with the n+1 and on save updates the dropdown and shows the new record number?

Regards,
Scott

by scott_heath

I have a tight schedule tomorrow, but I think I can do it by Monday.

Regards,
Scott

by maevinn

Set the field to Select Max(NumberField)+ 1? Max pulls the highest number, not the last one, though...

This discussion is closed to new replies.