Solved

Help with a form recordsource problem

Posted on 2006-06-21
8
444 Views
Last Modified: 2008-02-01
Hey experts,

I have a 2 part new data entry form. On the first form, unbound, the user enters in the PONumber, Vendor, etc. and clicks continue. This runs an insert query to add the data to the main table. I also added some code to store the PONumber in a module to be used as the record source criteria of the second form. The final bit of code for my continue button is this:

    mdlDatePicker.SetID = txtPurchaseID.Value

    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmNewPOStep1"

Then the code in my module is this:
Public Property Get ID() As Integer
    ID = intID
End Property
Public Property Let SetID(x As Integer)
    intID = x
End Property

And then my query that's used for my recordsource on the second form is this:

SELECT tblPurchases.*
FROM tblPurchases
WHERE tblPurchases.PurchaseID=id();

If I run the query by itself, it gives me the correct record information, but when I go through the new order process and go to the second form, no information is being pulled and my bound textboxes aren't showing any data. I'm stuck as how to fix this. Thanks for any help.

Bryan
0
Comment
Question by:bng0005
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 16953024
If the query is giving teh correct info, then teh recordsource is fine.
It's probably your form that needs refreshing to show the changes:


'(On the form On Open event)
Me.Form.Requery
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953047
no luck with that.

Tried in on open and on load, with both requery and refresh
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 16953068
Another method is to pass the recordsource across at form open:

Dim mySQL as String
mySQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & ID & ";"
DoCmd.OpenForm "frmNewPOStep2",,,,,OpenArgs = mySQL


then, on the form open

Me.Form.Recordsource = OpenArgs
Me.Form.Requery


Idea..?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:bng0005
ID: 16953143
Getting an invalid use of Null, saying that OpenArgs is null

Here's my updated code

    strSQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & txtPurchaseID.Value & ";"
    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , OpenArgs = strSQL
    DoCmd.Close acForm, "frmNewPOStep1"

I get the null on the  Me.Form.Recordsource = OpenArgs in the frmNewPOStep2 on open event
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 500 total points
ID: 16953170
OK.
In a Module, add this:

Public myFormSQL as String

Then, change your code to this:

myFormSQL = "SELECT * FROM tblPurchases WHERE PurchaseID=" & txtPurchaseID.Value & ";"
    stDocName = "frmNewPOStep2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmNewPOStep1"


On the form open of frmNewPOStep2:

Me.Form.Recordsource = myFormSQL


This is using a global variable to hold teh value
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953228
No errors, but no data showing up either. Before trying something else, how would I set the controlsource for the textboxes this way, through code as well, ie
   txtPurchaseID.ControlSource = ?
0
 
LVL 1

Author Comment

by:bng0005
ID: 16953553
ok well I deleted the control source of the textbox and tried adding in through vba once the recordsource was set. Here's my form on load event now

    Me.Form.RecordSource = myFormSQL
    Me.txtPurchaseID.ControlSource = Me.Recordset("PurchaseID").Name
    Me.Form.Requery

but still nothing showing up.

I'll explain what I'm trying to do as you might have an easier way than I thought up that would work. The 2 forms are used to enter a new Purchase Order into the database.  I have 2 main tables, tblPurchases and tblPurchaseItems, with 1-M relationship. My first form just saves the bare minimum of the information so I can have a PurchaseID in the database. The second form would use the PurchaseID as the master field to link with my subform so I can allow the user to enter in multiple items on the subform in datasheet view, and then update the main table with any additional information entered in by the user. Hope that all made sense.
0
 
LVL 1

Author Comment

by:bng0005
ID: 16954723
Dunno how I got it working, but I tried another compact and repair then redid the form from scratch and it's working like it should. Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question