Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with a form recordsource problem

Posted on 2006-06-21
8
Medium Priority
?
449 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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