entry form

[jaykappy]

I am trying to figure out how to create a form that will list all the records from a table.
When the user selects one of those records, textboxes on the form will populate with that records values allowing the user to modify or add new records.


How do I do this?

I have the table
I have a query that grabs the fields I want in the table to show up in the list.
I then need a bunch of textboxes that show those fields as well as a couple others that are not in the list but are in the query.

THanks

[June7]

You want a list of records (a listbox?) that when one item is clicked will populate a form? One way might be to have the form's RecordSource refer to listbox column with unique ID as filter criteria. Then in AfterUpdate event of listbox refresh the form. But that might prevent adding new records (probably main reason I avoid setting dynamic parameters in queries). Another method is to use VBA to set Filter and FilterOn properties of the form.

[jaykappy]

You want a list of records (a listbox?) that when one item is clicked will populate a form? One way might be to have the form's RecordSource refer to listbox column with unique ID as filter criteria. Then in AfterUpdate event of listbox refresh the form. But that might prevent adding new records (probably main reason I avoid setting dynamic parameters in queries). Another method is to use VBA to set Filter and FilterOn properties of the form.

how would the syntax go in teh recordsource to set the form to the selected listbox record?

[jaykappy]

Something like this?

SELECT tbl_V4_Lessons.ID, tbl_V4_Lessons.LessonID,tbl_V4_Lessons.LessonSubje ct,tbl_V4_Lessons.LessonName,tbl_V4_Lessons.Lesson Description
FROM tbl_V4_Lessons
WHERE (((tbl_V4_Lessons.ID)= List1.ID));

[jaykappy]

The above works in the RecordSource but it asks me for the ID when the form opens....

But How can I adapt this to VBA code and place it in say Change Event of the listbox

I guess I am askign how to write the VBA to Set the RecordSource of the form after the listbox selected value changes

[June7]

WHERE (((tbl_V4_Lessons.ID)= Forms!List1!ID));

I would use AfterUpdate event.

As I said, that might cause issue with adding records. But see what happens.

[jaykappy]

I tried this in the AfterUpdate of the Listbox
I get a datatype mismatch error

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = '" & Me![List16] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[June7]

Is the ID field number datatype? If so, don't use apostrophe delmiters. Apostrophe is for text, # for date, nothing for number.

[jaykappy]

I am confused now....

Form RecordSource:

SELECT tbl_V4_Lessons.ID, tbl_V4_Lessons.LessonID,tbl_V4_Lessons.LessonSubje ct,tbl_V4_Lessons.LessonName,tbl_V4_Lessons.Lesson Description
FROM tbl_V4_Lessons
WHERE (((tbl_V4_Lessons.ID)= Forms!List1!ID));


ListBox Query:
SELECT [qry_V4_AllRecords_NewLessons].[ID], [qry_V4_AllRecords_NewLessons].[LessonID], [qry_V4_AllRecords_NewLessons].[LessonSubject], [qry_V4_AllRecords_NewLessons].[LessonName], [qry_V4_AllRecords_NewLessons].[LessonDescription] FROM [qry_V4_AllRecords_NewLessons]


What do I do in the Listbox16 After Update?

[jaykappy]

I have to above 2 things and then this in the Listbox After Update event

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = Me![List16]"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I get an error...cant find List16...Bad Syntax?

[jaykappy]

Think I got it...one second

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me![List16] & ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

NO STRIKE THAT STILL NOT CHANGING the form when I select the record in the listbox...I have 3 bits of code...the 2 in my prior post (one recordsource for the Form, the other RowSource set to a query)...
Then the 3rd bit above...trrying to do this on the AfterUpdate event on the listbox16

uggggg

[June7]

No dot between Recordset and Clone. Here's how I use the RecordSetClone:

With Me.RecordsetClone
.FindFirst "LabNum='" & Me.tbxLABNUM & "'"
If .NoMatch = False Then
    Me.Bookmark = .Bookmark
End If
End With

The EOF test should work also.

[jaykappy]

This is all the code I have: I click the listbox and nothing happens....thank you for your help...I know this is not that complicated....just not doing something right....uggg


Form RecordSource:
SELECT tbl_V4_Lessons.ID, tbl_V4_Lessons.LessonID,tbl_V4_Lessons.LessonSubje ct,tbl_V4_Lessons.LessonName,tbl_V4_Lessons.Lesson Description
FROM tbl_V4_Lessons
WHERE (((tbl_V4_Lessons.ID)= Forms!List1!ID));

ListBox Query:
SELECT [qry_V4_AllRecords_NewLessons].[ID], [qry_V4_AllRecords_NewLessons].[LessonID], [qry_V4_AllRecords_NewLessons].[LessonSubject], [qry_V4_AllRecords_NewLessons].[LessonName], [qry_V4_AllRecords_NewLessons].[LessonDescription] FROM [qry_V4_AllRecords_NewLessons]

AfterUpdate On Listbox16:
Private Sub List16_AfterUpdate()
With Me.RecordsetClone
.FindFirst "ID=" & Me![List16] & ""
If .NoMatch = False Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

[June7]

Is the listbox name List1 or List16? One of those needs to be fixed.

I use dot (.) instead of bang (!) whenever possible because it will provoke intellisense popup tips in VBA.

[jaykappy]

I do this and I can get the ID returned in the message box....Its the Primary Key Autonumber field integer

But the form value does not change....uggggg

Its List16....do i need to refresh the main form?

Dim Test As Integer
Test = Me.List16.Value
MsgBox Test

With Me.RecordsetClone
.FindFirst "ID= " & Me.[List16] & ""
If .NoMatch = False Then
Me.Bookmark = .Bookmark
MsgBox "Nothing"
End If
End With