Open specific record from subform (1 Viewer)

[tfaisal]

Hello
There is form in attached database 'permits in progress' i wat to click REF# that is already hyperlinked and it should open order form on a specific form.I have applied same technique by macro on several forms and it works fine but couldnt figure out with this specific form.when i click the REF# it creates a new record in table .
Please help me fix it
Attached is database

 

[CJ_London]

I don't use macros, almost impossible to debug, but your looks straightforward enough and does not look wrong to me.

So take a look at the recordsource for the OrderFinal form. You join the order table to the Axle table, but do not use any data from Axle. And your problem exists because there are no matches for orders with a status of "Active" Or "Ordered" Or "Holding", only "Permit Back".

Remove the Axle table from the OrderFinal recordsource and you will get records. Not clear to me what the intention is, but change the OrderFinal allow additions property to false and you won't get new records

I would avoid spaces and non alphanumeric characters in your field names (#,%,?) - using them can cause unexpected and misleading errors, even when 'protected' with square brackets.

Make use of relationships and standardise field names - matching REF# to OID is not something that is obvious

 

[Pat Hartman]

There are several problems.
1. If you select an Order without an axle, you get no results. You are not selecting any columns from this table so as CJ mentioned, Axle should not be in the query at all.
2. It is poor practice to dirty a record before the user does, that is why the "empty" records are being created. If you want to provide defaults, set the default property of the column on the table or the control on the form. Either works with Jet/ACE BE's, but table defaults are better. If your BE is ODBC, you probably should set both since SQL Server et al will NOT populate the defaults until the record is saved and this may be disconcerting to the user or may cause you validation issues if a field with a default is required. If you want to set the defaults with code, put the code in the Form's BeforeInsert event so the code won't run until the user has typed the first character into the form so Access (and you) KNOWS he actually wants to create a new record.
3. I'm not sure why you have a hard-coded date/time in this code. I'm pretty sure that using Date() (If you want only date) or Now() (if you want date and time of day) would be more appropriate.
4. You have no validation code to stop bad records from being saved. I'm sure at least a couple of the columns are required, that validation code needs to go into the Form's BeforeUpdate event and possibly also set the Required property of the column on the table also to required.
5. In addition to the non-obvious names for the join between Order and Axle, the Dimensions table would have to join to the Order table and not the Axle table. Is that what you want?

To emphasize CJ's points, we are at a loss with your relationships because we have no way of identifying which fields relate to others without an established relationship in the Relationships window or some coherent naming standard. Joins in queries are NOT relationships. Naming multiple PKs "ID" is especially unhelpful if you are going to be sloppy about not defining relationships.