Form Field Hyperlink to open record in another form (1 Viewer)

kobiashi · May 12, 2018

hi

i am trying to open a record into another form from a hyperlink in a field,

i have the VBA code of

Code:

Private Sub txtTancNumber_Click()
    Const cstrForm As String = "frm_NewCavityTanc"
    DoCmd.OpenForm cstrForm, WhereCondition:="[Tanc_ID]=" & Me.txtTancNumber
End Sub

When I click on the hyperlink, a pop up box appears asking for the Tanc_ID,

how can i stop this and open the record straight into the form

June7 · May 12, 2018

If Tanc_ID is actually a text field, parameter needs apostrophe delimiters.

DoCmd.OpenForm "frm_NewCavityTanc", , , "Tanc_ID='" & Me.txtTancNumber & "'"

Why are you declaring a constant for form name?

kobiashi · May 12, 2018

Hi thanks for the reply

I've only just started using Access, so I'm still learning.

not sure if this matters for the issue, but this form is bound to a query

kobiashi · May 12, 2018

Tanc_ID is in the query

Tanc_ID is the primary key in a table so its an autonumber

June7 · May 12, 2018

Although not really correct usage, I think the Const should work but it is unnecessary complication. Eliminate and explicitly reference form name in the OpenForm command, without the apostrophes. That should also work.

kobiashi · May 12, 2018

so is this correct?

Code:

Private Sub txtTancNumber_Click()
    DoCmd.OpenForm frm_NewCavityTanc, WhereCondition:="[Tanc_ID]=" & Me.txtTancNumber
End Sub

June7 · May 12, 2018

The explicit form name must be within quotes as it is a string, not a declared variable. See my example again.

kobiashi · May 12, 2018

so this is what i've got

Code:

Private Sub txtTancNumber_Click()
    DoCmd.OpenForm "frm_NewCavityTanc", WhereCondition:="[Tanc_ID]=" & Me.txtTancNumber
End Sub

see screenshot for the outcome

June7 · May 12, 2018

What happens when you click OK on that popup?

What is the SQL statement of the form RecordSource?

You might have to provide db for review because that code should work.

kobiashi · May 12, 2018

When i click on the popup, the form opens but is empty

Record source for the form is Query_Tanc, which is the query i want to use

database is too bog to upload

but i can provide google drive link

kobiashi · May 12, 2018

database is too big to upload

SQL query for Query_Tanc is SELECT

https://drive.google.com/open?id=1zSxr2TQ8krUz-zxx-JXkBTuQuehhU7GG

here is a link from my google drive

June7 · May 12, 2018

Next time, run Compact & Repair then send the file to a Windows Compressed folder and attach the zip.

kobiashi · May 12, 2018

June7 said:
Next time, run Compact & Repair then send the file to a Windows Compressed folder and attach the zip.

will do thanks

June7 · May 12, 2018

Tanc_ID is not a field in that RecordSource query. TancNumber is.

The field is not in the field grid.

kobiashi · May 12, 2018

it is on mine, maybe i didnt save it before uploading it to my drive

June7 · May 12, 2018

Well, I add field to the design grid and code works.

kobiashi · May 12, 2018

are you talking about this view?

June7 · May 12, 2018

frm_NewCavityTanc is bound to Query_NewCavityTanc which did not have Tanc_ID field selected.

So, no, not the query in the image you posted.

kobiashi · May 12, 2018

Ah, i understand now

i've added it to Query_NewCavityTanc

now the form opens, but the record is empty

June7 · May 13, 2018

Okay, error goes away. Sorry, didn't look at textbox. The textbox has TancNumber value, not Tanc_ID.

Change code to reference Tanc_ID field, not the textbox. Or put another textbox on form bound to Tanc_ID (can be not visible).

DoCmd.OpenForm "frm_NewCavityTanc", WhereCondition:="[Tanc_ID]=" & Me!Tanc_ID

Form Field Hyperlink to open record in another form (1 Viewer)

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread