Open form based on ID nr from the main form and number of a button

[megatronixs]

Hi all,

I have a form that is based on an table called tbl_QC. I this table I have a review_id that is linked to the main table data. I have 5 buttons that create a QC recored int the tbl_QC. the review_id is passed, and the nr of the button for the QC. I would like to open the form based on the review id, that the first date is filled in and the qc_nr (that comes from the button and is passed with the "insert into")
My tbl_QC has the following fields (well, more of them, but the important ones I mention):
qc_id (autonumber)
date_1 (date passed via the button 1, the current date)
date_2 (date passed via the button 2 , the current date)
date_3 (date passed via the button 3 , the current date)
date_4 (date passed via the button 4 , the current date)
date_5 (date passed via the button 5 , the current date)
reveiw_id
qc_nr (it will be passed by the button used, 1, 2 and so on)

as you can see from below code, it is for the button 1:

Private Sub add_qc_1_Click()    ' to add the first QC to the table
Dim stDocName As String
Dim stLinkCriteria As String

On Error GoTo Err_add_qc_1_Click
    Dim strSql3 As String
    
    DoCmd.SetWarnings False
    'strSql3 = "Insert Into tbl_QC (review_id, date_1) Values (review_id = [Review_ID], Date())"
    strSql3 = "Insert Into tbl_QC (review_id, date_1, qc_nr) Values ([Review_ID], Date(), '1')"
    
    DoCmd.RunSQL strSql3
    DoCmd.SetWarnings True
    
     stDocName = "frm_QC_Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.add_qc_1.Enabled = False
Me.[final_4_eye_1st] = Date
Me.Refresh
Exit_add_qc_1_Click:
    Exit Sub
Err_add_qc_1_Click:
    MsgBox Err.Description
    Resume Exit_add_qc_1_Click
End Sub

Any way to pass the qc_nr to below code when I open the form?

Private Sub Form_Load()
 Me.review_id.Value = [Forms]![frm_Edit_Main_Reviews]![review_id]
End Sub

Greetings.

 

[arnelgp]

instead of the Load event (not needed), you can pass the criteria to the form.

stDocName = "frm_QC_Form"
stLinkCriteria = "[review_id] = " & me.review_id
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[megatronixs]

Hi Arnelgp,

Your tip works nice, I still have plenty to learn I see.

And how to pass the qc_nr to this as well when opening the form to check the QC on the review_id and the qc_nr?

I'm using this code to open the form:

Private Sub btn_qc_1_Click()
On Error GoTo Err_btn_qc_1_Click
 
Dim stDocName As String
Dim stLinkCriteria As String

 stDocName = "frm_QC_Form"
    stLinkCriteria = "[review_id] = " & Me.Review_ID ' Here maybe I can put that this is qc_nr 1 and it should open the form based on review_id and qc_nr '1'
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        [Forms]![frm_QC_Form]!Label401.Caption = "QC Date 1"

Exit_btn_qc_1_Click:
    Exit Sub
Err_btn_qc_1_Click:
    MsgBox Err.Description
    Resume Exit_btn_qc_1_Click
    
End Sub

Greetings.

 

[Minty]

Simply add the criteria in as an extra part of the string;

Private Sub btn_qc_1_Click()
On Error GoTo Err_btn_qc_1_Click
 
Dim stDocName As String
Dim stLinkCriteria As String

 stDocName = "frm_QC_Form"
    stLinkCriteria = "[review_id] = " & Me.Review_ID  [COLOR="Red"]& " AND [qc_nr] = " & Me.yourQCControl_on_your_form  [/COLOR]
' Here maybe I can put that this is qc_nr 1 and it should open the form based on review_id and qc_nr '1'
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        [Forms]![frm_QC_Form]!Label401.Caption = "QC Date 1"

Exit_btn_qc_1_Click:
    Exit Sub
Err_btn_qc_1_Click:
    MsgBox Err.Description
    Resume Exit_btn_qc_1_Click
    
End Sub

If you don't hard code the qc number but put it in a control or maybe select it from a combo box you'll only need one button.

 

[megatronixs]

Hin Minty,

That did the trick
and so simple, never expected it like that

Thanks a lot.

Greetings.