Continuous Form (unbound fields)

[CD Tom]

I have a continuous form setup that the user enters a registration number, in the after update event I look up the register persons name and place it into an unbound field. My problem is when the second row puts up the name from the first record show up in the unbound field. I know why it's doing this but is there a way to have the name not show up until the registration number (a different one of course) is entered into the second record.

Thanks again for all your help.

[twinnyfo]

Tom,

This has to do with the continuous forms. Perhaps, use a single form that cycles through individual records?

[CD Tom]

Ok, now I'm going to sound dumb, on a single form how do you cycle through individual records, and make it look like a continuous form?

[twinnyfo]

Not a dumb question at all. You cannot use a single form to make it look like a continuous form.

Perhaps you could show us the form and the code behind it. Help us to understand what you are trying to do. It is a bit strange to have an unbound control on a continuous form, as once you change that control, it will be changed for every record.

If your user is pulling up the registration for one person, then I'm not sure you need a continuous form--single form should serve your purpose; just filter by the selected registration number.

[CD Tom]

OK, I understand what's happening and that's what I'm trying to do away with. This is and transaction input form and the user will be entering maybe 15 or 50 transactions a day. When he/she enters the registration number they want to see the customer associated with that registration number to verify it's the correct person, that name comes from the customer table. There is also another field they want to show which comes from another table. The data from the input form will go into the transaction table which will contain only the registration and customer number and of course other transaction information. And as you say for each transaction when the user enters the registration number the lookup information shows on the next record. I've tried a couple of different ways to lookup the data. In the unbound field I've done a Dlookup to find the customers name and also the registered boat's name, I've also entered information in the AfterUpdate event of the registration number but just get the same result.
I hope this makes some kind of sense.
Thanks again for your help.

[twinnyfo]

Tom,

It does make sense. Let me throw this concept off you to see if I understand what you are trying to do:

A User enters a Registration number
The Form pulls up the Customer associated with that registration number
Other "Data" from another "Table" is also displayed
The User then adds data to a Transaction Form capturing data associated with this Customer/Registration Number

Is this correct?

First, I would need a snap shot of your relationships--particularly as it releates to the Customers Table, Registration Number, the Transanctions and this "Other Table" (please grab a screen shot and attach to a Word Doc, as this will display better as resolution is limited on images. Please also include in your response the data types of your key fields (you could do a screen shot of each table in Design view).

My thought is that what you are trying to do is very easy by using a subform, with the established relationships between tables, such that the User just Enters a Reg Number, selects/verifies the Customer and click "Add Transaction" and the subform is now available, and because of the relationships, would add a record related to that customer/Reg Number.

We may also have to talk about database normalization.. ..

[Seth Schrock]

Why do you have it unbound? You should have a transaction table that is related to both the customer table and the "other table" you mention. You then create a query that includes the fields from the transaction table and the fields needed from the two related tables. When you enter a registration number, the joins make the data pop appear automatically without any code.

For example, using the Northwind sample database (if you haven't already, you should download it), run the following query (I'm using the 2007 Northwind version which works for Access 2007, 2010 and I think 2013 as well):

SELECT [Order Details].ID
, [Order Details].[Order ID]
, [Order Details].[Product ID]
, Products.[Product Name]
FROM Products INNER JOIN [Order Details] 
ON Products.ID = [Order Details].[Product ID];

Scroll down to where you see the new record. The product field will be a combobox. Select a product and you will see the product name appear in the Product Name field. This obviously is redundant to the name that shows in the Product field's combo box, but it demonstrates what you can do for your system.

[CD Tom]

I'll see about the screen shots, right now I've got to run and will not get back to this until tomorrow. Thanks for the suggestions and I'll continue this project later.
Thanks again.

[CD Tom]

I just got back to the form problem and have figured a way to make this work. What I did was in the unbound field that looked up the customer info I put an iif statement, so if the look up field was blank (meaning no registration number has been entered yet) if this is true then I just put " " (blank) and if there was a registration number present the I did the dlookup for the customer name. It worked great and the new line on the continuous form doesn't show the previous user information.