Form Grouping

[Desstro]

Attached is an image. Top is form I am working on creating, below it is a table that I am trying to get data from.



What I am trying to do...

I want to create another combo box for "PKG#" on the form...easy enough. But what I want the combo box to do is when a specific Package# is selected from the drop down I want the associated data from table to auto fill in under the correct areas on the form. i.e., Program to Program, RT to RT etc...

Any suggestions?

Also, you'll notice that in the top left of the form, it says nightshift...I would like to put a button in the header that allows me to switch between a day and a nightshift form that retains the values for each using the same form....is this possible and if so please help with that also.

[maximus]

I have a Table with the following Fields:
tblMember:
1) Member_Id (Auto Number Primary Key)
2) MemberName
3) DOB
4) F (Boolean)

I have a Combobox with the RowSource tblMember. I have used the Column Property to insert Data in my Table. The Code is attached AfterUpdate Event of the Combo Box.

Private Sub Combo0_AfterUpdate()
Set rst = CurrentDb.OpenRecordset("tblmember")
rst.AddNew
'--- transfer data from Combo box to table fields
rst!MemberName = Me.Combo0.Column(1)
rst!DOB = Me.Combo0.Column(2)
rst!f = Me.Combo0.Column(3)
rst.Update '--- save the record
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using
End Sub

Remember Column Index of a Combo box starts from 0 and not 1

[maximus]

To change the caption of the form I would do something like this:

Me.Caption=Me.Combo0.Column(1)

This code goes on the after update event of the combo box

[Desstro]

Thanks Maximus, but looking at the code I don't think I explained my self well enough.

Everything on the form currently is unbound.

Attached is a new image.

On the form under the PKG# heading is going to be combo boxes that show the packages entered into the table.

When I choose a package, "1" or "2" from the combo box on the form I want also on the form there to be a field under the "Program", "Area" and "Location" that will automatically fill in according to the like fields in the table.

I'm going to ignore the "RT" "UT" etc... area for now because that is going to have to be a calculated field between the completed and remaining area's.

Could you help me out with a specific code? I'm pretty green with Access.

oops forgot to attach image.....

[Desstro]

new attachment

[Desstro]

I tried your code Maximus, I got an error. See image.

[maximus]

This is code that you can use.

the Combo box is unbound and the Rowsource is a value list. When I select a ID Number of a Member the Name, DOB and And Registration is auto populated on the form.

MemberName
DOB
Registration are text Boxes on the form.

This code is attached to the AfterUpdate Event of the Combo Box.




Dim StrSQL As String
Dim rs As Object

StrSQL = "Select * From tblmember Where Member_id=" & Me.Combo2
Set rs = CurrentDb.OpenRecordset(StrSQL)

If rs.EOF And rs.BOF Then
MsgBox "no Entries Found"
Exit Sub
End If

Me.MemberName = rs!MemberName
Me.DOB = rs!DOB
Me.Registration = rs!Registration
rs.Close
Set rs = Nothing
End Sub

Regardin the Error:
1) Check the name of the Combo box is it Combo0. I see that u have used Comb0 in the code.
2) set your references to Microsoft DAO 3.6 Object Libary

[Desstro]

Im having a hard time finding the Objesct Library in 2010 in order to set as DAO 3.6

Do you know the path?

[maximus]

You can reference to the object libary like this:

dim db as dao.database
dim rs as dao.recordset
dim StrSQL as string

StrSQL = "Select * From tblmember Where Member_id=" & Me.Combo2

set db = currentdb
set rs = db.openrecordset("strSQL")


If rs.EOF And rs.BOF Then
MsgBox "no Entries Found"
Exit Sub
End If

Me.MemberName = rs!MemberName
Me.DOB = rs!DOB
Me.Registration = rs!Registration
rs.Close
Set rs = Nothing
End Sub


to learn more follow this link:
http://support.microsoft.com/kb/225962
http://www.accessmvp.com/TWickerath/articles/adodao.htm

[Desstro]

OK... this seems like it should work...what am i doing wrong here? I put text boxes on the form that coincide with the data I am trying to auto-pop.

See image for error.

[Desstro]

Says this sub or function not defined?

[maximus]

set rs = db.openrecordset(strSQL)

remove the quotations. and put the . after db

[Desstro]

Method or data member not found