INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

set recordsource of another form before opening it - AND show a specific record

set recordsource of another form before opening it - AND show a specific record

set recordsource of another form before opening it - AND show a specific record

(OP)
I have two forms -- formA and formB.

I want to create a button on formA that will open formB, change the recordsource, and then match the customerID

My situation is similar to the one in this thread, but with the added
aspect of matching the customerID. I can't find a way to make it work.

(I'm a solopreneur, and this is my customer database. I'm not a full-time programmer. My understanding of VBA is limited. But I can usually search forums like this one and achieve what I need. However this time, I'm stumped.)

Here is my code. (This works for pulling up the correct record in formB with the default recordsource. Now I need it to change the recordsource first, and then match customerID.)

CODE -->

'------------------------------------------------------------
' Command30_Click
'
'------------------------------------------------------------
Private Sub Command30_Click()
On Error GoTo Command30_Click_Err

    Docmd.OpenForm "formB", acNormal, "", "[customerID]=" &" '" [&customerID] & "'", , acNormal


Command30_Click_Exit:
    Exit Sub

Command30_Click_Err:
    MsgBox Error$
    Resume Command30_Click_Exit

End Sub 

Is there a way to do this?

Thanks.

RE: set recordsource of another form before opening it - AND show a specific record

You could open it and set the recordsource. Then apply the filter

Docmd.OpenForm "formB", acNormal
with Forms!FormB
.recordsource = "newRecordsource"
.requery
.filter = "[customerID]=" &" '" [&customerID] & "'"
'remove single quotes if ID is numerice
.filteron = true
end with

RE: set recordsource of another form before opening it - AND show a specific record

In that thread there are other ways to do it posted by Duane Hookum. You could have the form based on a saved query and prior to opening it change the sql of the saved query. Something like

dim db as dao.database
dim qdf as dao.querydef
dim strSql as string
set db = current db
strSql = "Select..... from tblName where customerID = '" & me.customerID & "'"
db.querydefs("yourqueryname").sql = strsql

now open form

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close