?
Solved

Help with a form recordsource problem

Posted on 2006-06-21
8
Medium Priority
?
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

770 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