Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2008, 08:25 PM
Registered User
 
Join Date: Nov 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populating an unbound form from a table

Here is the set up. A record was created using the following code from an unbound form:
' Create a variable to assign the New Rma Number in #-# string format
Dim sNewRmaNumber As String

' Create an integer variable to hold New RMA Header value
Dim iNewRmaHeader As Integer

' Create a variable to hold SQL Statement
Dim sSQL As String

' Create a variable to hold Expedite order checkbox value
Dim sExpediteChkBx As String

' Create a variable to hold NIST Req checkbox value
Dim sNistChckBx As String

'----------------------------

' Run SQL to gahter data and record into table
sSQL = "INSERT INTO [tbl-RmaNumberLog] " & _
"([RmaExpeditedOrder],[RmaCompanyName],[RmaContactName],[RmaStreetAdd1]," & _
"[RmaStreetAdd2],[RmaCity],[RmaState],[RmaZipCode]," & _
"[RmaCountry],[RmaEmail],[RmaTelephone],[RmaExt]," & _
"[RmaFax],[RmaPONumber],[RmaNistCertification],[RmaCarrierAcc]," & _
"[RmaNotes],[RecordCreator],[RmaNumber],[RmaNumberHeader])" & _
"VALUES (" & sExpediteChkBx & ",'" & Me!TextRmaCompanyName & "','" & Me!TextRmaContactName & "','" & Me!TextRmaStreetAdd1 & "'," & _
"'" & Me!TextRmaStreetAdd2 & "','" & Me!TextRmaCity & "','" & Me!TextRmaState & "','" & Me!TextRmaZipCode & "'," & _
"'" & Me!TextRmaCountry & "','" & Me.TextRmaEmail & "','" & Me!TextRmaTelephone & "','" & Me!TextRmaExt & "'," & _
"'" & Me!TextRmaFax & "','" & Me!TextRmaPONumber & "'," & sNistChckBx & ",'" & Me!TextRmaCarrierAcc & "'," & _
"'" & Me!TextRmaNotes & "','" & VarUserName & "','" & sNewRmaNumber & "'," & iNewRmaHeader & ")"

' Execute SQL Statement
DoCmd.RunSQL sSQL


This created the file on the table "tbl-RmaNumberLog". What I am trying to do is to pull the record by filtering the set by three fields: RmaNumber (Key Field, Format = Text) , RmaVoid (True/False), RmaShipped (true/False). The RmaNumber value to filter by is located in “RmaRecordUpdate.TextRmaSelectedRecordNumber” form and field. The other two (true/false) fields are to be false (basically stating that the record has not been cancelled or completed, if either field is true this would mean this record is no longer active and cannot be edited.) If a record is found all the fields should be populated with the right data. This is where I am stuck. I keep getting the ever loved syntax error and the expected # error. I have tried a few approaches to the problem but have not resolved it as of yet. Any help would be appreciated. Here is an example of the code I have tried.

Dim db as database
Dim rs as recordset
Set db = CurrentDb
 'Set rs = db.OpenRecordset("SELECT [tbl-RmaNumberLog].RmaExpeditedOrder,[tbl-RmaNumberLog].RmaNumber,[tbl-RmaNumberLog].RmaCompanyName,[tbl-RmaNumberLog].RmaContactName,[tbl-RmaNumberLog].RmaStreetAdd1,[tbl-RmaNumberLog].RmaStreetAdd2,[tbl-RmaNumberLog].RmaCity,[tbl-RmaNumberLog].RmaState,[tbl-RmaNumberLog].RmaZipCode,[tbl-RmaNumberLog].RmaCountry,[tbl-RmaNumberLog].RmaEmail,[tbl-RmaNumberLog].RmaTelephone,[tbl-RmaNumberLog].RmaExt,[tbl-RmaNumberLog].RmaFax,[tbl-RmaNumberLog].RmaPONumber,[tbl-RmaNumberLog].RmaNistCertification,[tbl-RmaNumberLog].RmaCarrierAcc,[tbl-RmaNumberLog].RmaNotes,[tbl-RmaNumberLog].RmaVoid " & _
'"FROM [tbl-RmaNumberLog] " & _
'"WHERE ((([tbl-RmaNumberLog].RmaExpeditedOrder)=me.TextRmaSelectedRecordNumber ) AND (([tbl-RmaNumberLog].RmaVoid)=False)) AND (([tbl-RmaNumberLog]. RmaShipped)=False))")

Thanks for the help............ Luis


 
Old November 18th, 2008, 03:15 PM
Registered User
 
Join Date: Nov 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For got to put the code that places the data into the unbound fileds. Please note that the table has about eight more fileds that I do not need to upload to the form just the ones specified on the code....


' Transfer recorset values to the open form
Me.TextRmaContactName = CStr(rs![RmaContactName])
Me.TextRmaCompanyName = CStr(rs![RmaCompanyName])
Me.TextRmaStreetAdd1 = CStr(rs![RmaStreetAdd1])
Me.TextRmaStreetAdd2 = CStr(rs![RmaStreetAdd2])
Me.TextRmaCity.Value = rs!RmaCity
Me.TextRmaState.Value = rs!RmaState
Me.TextRmaZipCode.Value = rs!RmaZipCode
Me.TextRmaCountry.Value = rs!RmaCountry
Me.TextRmaTelephone.Value = rs!RmaTelephone
Me.TextRmaExt.Value = rs!RmaExt
Me.TextRmaFax.Value = rs!RmaFax
Me.TextRmaEmail.Value = rs!RmaEmail
Me.TextRmaPONumber.Value = rs!RmaPONumber
Me.TextRmaCarrierAcc.Value = rs!RmaCarrierAcc
Me.TextRmaNotes.Value = rs!RmaNotes
Me.CheckExpeditedOrder.Value = rs!RmaExpeditedOrder
Me.CheckNistCertification.Value = rs!RmaNistCertification
Me.TextRmaNumber = rs!RmaNumber


Thanks for the help.

Luis


 
Old November 18th, 2008, 03:16 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The first thing I would do is this:

 Create a variable to hold Expedite order checkbox value
Dim bExpediteChkBx As Boolean

' Create a variable to hold NIST Req checkbox value
Dim bNistChckBx As Boolean

Since your control is a check box, it stores True / False values, so the natural variable is a Boolean. This can get passed to your query without single quotes.


Also, I would be circumspect about creating an Integer variable where you might get an overflow error. I don't know what the RMA Header value is, but it is always a good idea to place generated integers in a Long variable to prevent overflow when you exceed the max integer value. So:

' Create an integer variable to hold New RMA Header value
Dim lNewRmaHeader As Long

I will have to look at the query string later. I know Old P will find a lot of issues that I might miss since I rely on the designer most of the time and he often nails me on SQL syntax.

HTHFN

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 18th, 2008, 03:20 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Once again you have this issue here:

"WHERE ((([tbl-RmaNumberLog].RmaExpeditedOrder)=me.TextRmaSelectedRecordNumber ) AND (([tbl-RmaNumberLog].RmaVoid)=False)) AND (([tbl-RmaNumberLog]. RmaShipped)=False))")

Should be more like:

"WHERE ((([tbl-RmaNumberLog].RmaExpeditedOrder)=" & me.TextRmaSelectedRecordNumber & ") AND (([tbl-RmaNumberLog].RmaVoid)=False)) AND (([tbl-RmaNumberLog].RmaShipped)=False))"

Is that working?

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 18th, 2008, 03:21 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh crap, I mean:

"WHERE ((([tbl-RmaNumberLog].RmaExpeditedOrder)='" & me.TextRmaSelectedRecordNumber & "') AND (([tbl-RmaNumberLog].RmaVoid)=False)) AND (([tbl-RmaNumberLog].RmaShipped)=False))"


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 20th, 2008, 04:10 PM
Registered User
 
Join Date: Nov 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Please note I had an airhead moment on my posting… In the WHERE statement, I had RmaExpeditemoment (a Boolean) compared to TextRmaSelectedRecordNumber (a text field) needless to say an error occurred. Noticed it from your post after I thought “what is he talking about” which was shortly followed by “OH, my bad”. Realizing this I figured I would fix it up and post good news with my airhead moment. Didn’t quite workout that way in the end. I’m still getting the “an expression you entered was the wrong data type for one of your arguments”. From what I can tell it’s the
“"WHERE ((([tbl-RmaNumberLog].RmaNumber) = '" & _
Me.TextRmaSelectedRecordNumber & "')

Section of the code. I removed the two other criteria and the error remained. The table Colum referred is a text Colum with field size of 7 and the text field on the form is unbound. So I can’t really see what’s wrong. Should I place the unbound field into a variable and use it? Can the code be written to state the table Colum type when the data is selected?

Also does the version off access one is running be a major cause for the problem. I am still running 2003, is the code from 2007 cause programming errors or are they still pretty similar?

Any help would be appreciated, (by the way thanks for the pointer in using Long vs Integer) Please see my current code below:


Dim db As Database
Dim rs As Recordset
Dim tmpUser As String

' Create a variable to hold Expedite order checkbox value
Dim bExpediteChkBx As Boolean

' Create a variable to hold NIST Req checkbox value
Dim bNistChckBx As Boolean

' Create an integer variable to hold New RMA Header value
Dim lNewRmaHeader As Long

' Create variable to hold Value of RMA desired
Dim sRecordFilter As String

'----------------------------
' Focus on the database your currently in
Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT [tbl-RmaNumberLog].RmaExpeditedOrder, " & _
"[tbl-RmaNumberLog].RmaNumberHeader, [tbl-RmaNumberLog].RmaNumber, [tbl-RmaNumberLog].RmaCompanyName, " & _
"[tbl-RmaNumberLog].RmaContactName, [tbl-RmaNumberLog].RmaStreetAdd1, [tbl-RmaNumberLog].RmaStreetAdd2, " & _
"[tbl-RmaNumberLog].RmaCity, [tbl-RmaNumberLog].RmaState, [tbl-RmaNumberLog].RmaZipCode, " & _
"[tbl-RmaNumberLog].RmaCountry, [tbl-RmaNumberLog].RmaEmail, [tbl-RmaNumberLog].RmaTelephone, " & _
"[tbl-RmaNumberLog].RmaExt, [tbl-RmaNumberLog].RmaFax, [tbl-RmaNumberLog].RmaPONumber, " & _
"[tbl-RmaNumberLog].RmaNistCertification, [tbl-RmaNumberLog].RmaCarrierAcc, [tbl-RmaNumberLog].RmaNotes, " & _
"[tbl-RmaNumberLog].RmaShipped, [tbl-RmaNumberLog].RmaVoid " & _
"FROM [tbl-RmaNumberLog] " & _
"WHERE ((([tbl-RmaNumberLog].RmaNumber) = '" & Me.TextRmaSelectedRecordNumber & "') And (([tbl-RmaNumberLog].RmaShipped) = False) And (([tbl-RmaNumberLog].RmaVoid) = False))")

‘ Run Sql
DoCmd.RunSQL rs

PS: Per you observation, the RmaNumber and the RmaHeader are related. They work to relate items in a backorder record format. When work is requested it is assigned the next available number via code regardless of quantity of items in the order, let’s say that next number is 10 (kind of like getting the number in line at the butcher). Now let’s say there are 3 items to be processed (your going to buy pork, Chicken, fish). We could process all 3 at one time, two in one run and a single in another run, or we could do three runs individually. This is done to allow things to move independently of each other in case of a delay. If all items run together a delay in one would cause a delay in all ( Sorry out of fish, well deliver the complete order in 2 days when it comes in). If you process them separately then a delay in one causes a delay in only one. So a backorder format is used. The original order will get the 10-0 (RmaNumber) designation. 10 (header) – 0 (Packet #) means only one packet was issued any additional packets will increase the second number accordingly. This is intended to work into the structure I’m trying to crate. I can use the header number to get a total count of items in the order or I can look for the specific order via the RmaNumber. That is why the Header is a long integer while the RmaNuber is a text (accommodate the “-“ in the string)



 
Old November 20th, 2008, 04:21 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It is always better to take values in variables for data validation, if you are doing that on the form, and not a good idea to take values at runtime. This:

Set rs = db.OpenRecordset("...")

Is runtime. Do this instead:

sSQL = "..."

Set rs = db.OpenRecordset(sSQL)

Also, change this:

"WHERE ((([tbl-RmaNumberLog].RmaNumber) = '" & Me.TextRmaSelectedRecordNumber & "') And (([tbl-RmaNumberLog].RmaShipped) = False) And (([tbl-RmaNumberLog].RmaVoid) = False))")

to this:

"WHERE tbl-RmaNumberLog.RmaExpeditedOrder = '" & me.TextRmaSelectedRecordNumber & "' AND tbl-RmaNumberLog.RmaVoid = False AND tbl-RmaNumberLog.RmaShipped = False"

Better yet:

sRMA_SRN = Me.TextRmaSelectedRecordNumber

"WHERE tbl-RmaNumberLog.RmaExpeditedOrder = '" & sRMA_SRN & "' AND tbl-RmaNumberLog.RmaVoid = False AND tbl-RmaNumberLog.RmaShipped = False"

There is no need for all those parens and brackets. That looks like it is copied and pasted from SQL View. If you are passing a string, there is no need for it and it is much easier to read without it.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 20th, 2008, 07:08 PM
Registered User
 
Join Date: Nov 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Solved it!!! Thanks for the help. As you pointed out I did copy it from the SQL view of the query. I saw this mentioned in a post and figured that it was worth a shot.

 From the other post and this one it seems that it’s not a good idea to have access do multiple things in one command.

I tried the code as you had written it without all parens and extra stuff. When I did that it gave me the “To few parameters. expected 4” When I reinstated the query SQL format it was happy (after I made your other suggested modifications) and completed the function flawlessly. Do you have any idea why? Cold it be an issue rooted in 2003 to 2007 code change? Or is it possibly something else.

I have included the final revision of the code I am using for show and tell purposes.

Just one more thanks for the assist mmcdonald

------MS Access 2003------

' Create Recordset variables
Dim db As Database
Dim rs As Recordset

' Create a variable to hold Expedite order checkbox value
Dim bExpediteChkBx As Boolean

' Create a variable to hold NIST Req checkbox value
Dim bNistChckBx As Boolean

' Create an integer variable to hold New RMA Header value
Dim lNewRmaHeader As Long

' Create variable to hold Value of RMA desired
Dim sRecordFilter As String

' Create variable to hold SQL string
Dim sSQL As String

' Transfer RMA requested to variable
sRecordFilter = Me.TextRmaSelectedRecordNumber

'----------------------------

' Focus on the database your currently in
Set db = CurrentDb

' Load Sql into variable
sSQL = "SELECT [tbl-RmaNumberLog].RmaExpeditedOrder, " & _
"[tbl-RmaNumberLog].RmaNumberHeader, [tbl-RmaNumberLog].RmaNumber, [tbl-RmaNumberLog].RmaCompanyName, " & _
"[tbl-RmaNumberLog].RmaContactName, [tbl-RmaNumberLog].RmaStreetAdd1, [tbl-RmaNumberLog].RmaStreetAdd2, " & _
"[tbl-RmaNumberLog].RmaCity, [tbl-RmaNumberLog].RmaState, [tbl-RmaNumberLog].RmaZipCode, " & _
"[tbl-RmaNumberLog].RmaCountry, [tbl-RmaNumberLog].RmaEmail, [tbl-RmaNumberLog].RmaTelephone, " & _
"[tbl-RmaNumberLog].RmaExt, [tbl-RmaNumberLog].RmaFax, [tbl-RmaNumberLog].RmaPONumber, " & _
"[tbl-RmaNumberLog].RmaNistCertification, [tbl-RmaNumberLog].RmaCarrierAcc, [tbl-RmaNumberLog].RmaNotes, " & _
"[tbl-RmaNumberLog].RmaShipped, [tbl-RmaNumberLog].RmaVoid " & _
"FROM [tbl-RmaNumberLog] " & _
"WHERE ((([tbl-RmaNumberLog].RmaNumber) = '" & sRecordFilter & "') And (([tbl-RmaNumberLog].RmaShipped) = False) And (([tbl-RmaNumberLog].RmaVoid) = False))"

' Set sSql into rs variable - Create Record Set
Set rs = db.OpenRecordset(sSQL)

' Transfer recorset values to the open form
Me.TextRmaContactName = rs!RmaContactName
Me.TextRmaCompanyName = rs!RmaCompanyName
Me.TextRmaStreetAdd1 = rs!RmaStreetAdd1
Me.TextRmaStreetAdd2 = rs!RmaStreetAdd2
lNewRmaHeader = rs!RmaNumberHeader ' Set RmaHeader into variable(For possible use)
Me.TextRmaCity.Value = rs!RmaCity
Me.TextRmaState.Value = rs!RmaState
Me.TextRmaZipCode.Value = rs!RmaZipCode
Me.TextRmaCountry.Value = rs!RmaCountry
Me.TextRmaTelephone.Value = rs!RmaTelephone
Me.TextRmaExt.Value = rs!RmaExt
Me.TextRmaFax.Value = rs!RmaFax
Me.TextRmaEmail.Value = rs!RmaEmail
Me.TextRmaPONumber.Value = rs!RmaPONumber
Me.TextRmaCarrierAcc.Value = rs!RmaCarrierAcc
Me.TextRmaNotes.Value = rs!RmaNotes
bExpediteChkBx = rs!RmaExpeditedOrder ' rs to variable
    Me.CheckExpeditedOrder = bExpediteChkBx ' Transfer Variable to textbox
bNistChckBx = rs!RmaNistCertification ' rs to variable
    Me.CheckNistCertification = bNistChckBx ' Transfer Variable to textbox
Me.TextRmaNumber = rs!RmaNumber

' Close record set
rs.Close

' clear main variable values
Set rs = Nothing
Set db = Nothing
sSQL = ""


 
Old November 21st, 2008, 08:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That is an old issue with Access. But it is always a good coding practice to declare variables, put the values in the variables with data validation, build a SQL string as needed, and then execute the built strings as a single string variable.

I'm glad that worked out for you.


mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate unbound form with recordset pacg Access VBA 2 September 26th, 2005 06:06 PM
unbound field in a form zanza67 BOOK: Access 2003 VBA Programmer's Reference 0 September 3rd, 2005 01:46 PM
how to display recordset in unbound form huela Access VBA 0 October 12th, 2004 11:58 PM
Unbound Fields on a continuous form phil.t Access 0 October 5th, 2004 07:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.