Form to Output Table

[dwright]

My receipt number is an auto number from the previous form. I do not need all of the fields from the previous form, just a few relevant ones. I also did create a field in the receipt table that is check box that is labeled void. I am assuming the control source for the fields I want to show up are from the table?

[jzwp11]

receipt number is an auto number from the previous form

The previous form does not matter; it is what is in the table that is important. Is the receipt number the primary key of the ReceiptOutput table?

[dwright]

The Receipt Number is the primary key in the Receipt out put table.

[jzwp11]

OK, you should be able to adjust the code with the appropriate field and combo box name in order to search to get the receipt record that is selected in the combo box.

[dwright]

Do I have to have the receipt number in there twice being it's the primary key? Do I need to have it in the header? I put the receipt number field in the body of the form. No control source, the control source for the form is my receipt table. The row source for my receipt number is the receipt table. The event procedure after update is the following:

Private Sub ReceiptNumber_AfterUpdate()
Dim myrecset As Object
Set myrecset = Me.RecordsetClone.Clone
myrecset.FindFirst "[ReceiptNumber]=" & Me![ReceiptNumber]
Me.Bookmark = myrecset.Bookmark
End Sub

When I select a record it tells me there is no current record. Should I change the name of the combo box? I had it as Receipt number before & didn't change it.

[jzwp11]

Do I have to have the receipt number in there twice being it's the primary key?

myrecset.FindFirst "[ReceiptNumber]=" & Me![ReceiptNumber]

The reference in red represents the field name in the table or query that provides the record source for the form. The reference in green is the name of the control on the form (in your case the combo box that holds the receipt numbers)

...control source for the form is my receipt table.

The receipt table is the record source for the form not the control source.

The control source is where each control (textbox, combo box, list box) on the form gets its data i.e. the control is bound to a field in the record source (the control source property will list a specific field of the table to which the form is bound).

As to the combo box in the header, it must be an unbound control (i.e. the control source property should be blank). Since it is a combo box, it will have a row source (where the data comes from that supplies the combo box with data).

That probably sounds confusing, so the form should be bound to your receipt table (so the receipt table is the record source for the form).

The combo box control in the header is not bound to the record source of the form, and thus the control source property should be blank. But, the row source of the combo box will be your receipt table.

[dwright]

Sometimes I am not very good at explaining. I doubled checked everything. For this code:
myrecset.FindFirst "[ReceiptNumber]=" & Me![ReceiptNumber]
It is correct the first ReceiptNumber is my table & the second is my form.

The form "record source" is the correct table I misspoke with control source.

The only thing I had different with the combo box from what you said is that it wasn't in the header. So I put it in the header. This is where I am confused - sorry I feel stupid. If the Receipt Number is in the header do I have to have it again in the detail because when I go to view the form I don't see the header & don't see a way to make it visible. So I don't know how they select it.

[jzwp11]

myrecset.FindFirst "[ReceiptNumber]=" & Me![ReceiptNumber]

The red area must reference the FIELD of the table, in other words, your field name for the primary key of the receipt table. The green area must reference the specific control name of the combo box on the form not the form name.

If the Receipt Number is in the header do I have to have it again in the detail

You would have a bound textbox in the detail section of the form that would show the receipt number (the control source for the textbox would be the primary key field of the underlying table).

I go to view the form I don't see the header & don't see a way to make it visible

. If you go into design view of the form, you should see the form header. You will need to drag it down to open it up to put in the combo box.

See the attached; maybe it will help.

[dwright]

The red area does reference the field name in the table & the green area references the combo box in my form. That is why I said is it going to be a problem that I named it ReceiptNumber because I can change it. I have been back & forth trying different things that it just ended up that name.

I put the second text box on my detail part of my form which is bound to the key field of the table.

When I said I didn't see the combo box in the header I wasn't referring to the design view, I can see it & put the field there in the design view, when I go to the form view, what the user will see there is no combo box to choose from. They cannot enter into the text box to choose a receipt number.

[dwright]

I just played with this form and realized I had the combo box in the page header, not the form header. Every day I amaze myself and my stupid moves. I will keep playing with this. You really have helped me so much!!

[jzwp11]

You're welcome. Keep us posted on your progress.

[dwright]

I was just able to get to my form today I am wearing too many hats at work.
I have my combo box in my "form" header (vs the page header!!). I have the record source for the form set to my receipt table. I have the necessary fields in the detail portion of the form, with their control source set to the related field in the receipt table.
I must note that I changed my combo box from "ReceiptNumber" to "combobox1" to avoid confusion (mine of course). In my combo box I have the following after update event:

Private Sub combobox1_AfterUpdate()
Dim myrecset As Object
Set myrecset = Me.RecordsetClone.Clone
myrecset.FindFirst "[ReceiptNumber]=" & Me![combobox1]
Me.Bookmark = myrecset.Bookmark
End Sub

When I am in the form view I click on the combo box & I have a list of receipt numbers to choose from. When I click on the receipt I get the following Microsoft Visual Basic error:

Run-time error '3021':
No current record.
End, Debug, Help

We know what end does. If I debug it opens visual basic & high lights the following line:

Me.Bookmark = myrecset.Bookmark

If I do help (which I may add that I have been needing a lot of lately) it tells me that there is either no record or it has been deleted, when in fact I can see the record in the table.

help?

[jzwp11]

What is the datatype of the receiptnumber field? When you first open your form, do you see the first record in the table?

[dwright]

The data type of the ReceiptNumber in the ReceiptOutput table is an auto number. When you open the void form you see the header with a combo box that has a drop down & you can choose a receipt number, in the detail are 8 fields from the receiptoutput table. This might not make a difference - but could this be my problem - when I created the receipt entry form & connected it to the receipt output table I made the receipt number an auto number in the table. Was there a way to make it that way in the form? Just a thought.

[jzwp11]

The autonumber is fine, I just wanted to make sure that it was not text. As to why it is not working properly I cannot say. Would you be able to post your database (with any sensitive data removed)? Or you can make a copy of the database and include only the structure and add some dummy data for illustration purposes and post that