Solved

Help with a form recordsource problem

Posted on 2006-06-21
8
443 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

792 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