Replies

Rob,
try:

If Me.Label_Checked = 0 And Not ([Me.Stock_Code_No_1] In (“1Z51”, “1Z52″,”1Z53”) ) Then

The idea being that you check if the item IS in the list, then reverse the true/false

Or, since you’re no longer in a text box but in VBA, split the test into two:

If Me.Label_Checked = 0 then

if ([Me.Stock_Code_No_1] In (“1Z51”, “1Z52″,”1Z53”) ) then

else

end if

end if

(apologies for not using code tags, I’m in a rush)

Cheers,

I have a listview (with the property fullrowset=true) how to controll in the event double click if the rowset clicked is filled?

Sorry Sal, I don’t understand the question. Are you trying to tell if an entry in a list box is selected or not? What are you after – or have you found the solution already?

Hi!

further to John’s reply, a quick glance indicates that 1) this is a complicated little system you’ve got here; and 2) yes, you could make life easer for yourself.

For example, you’ve got some code that is repeated for the 40+ different values of E5:

Code:

 
If Range("E4") = "Builder's Bene 2008" And Range("E5") = "PPO Plus" Then
	Windows("Library.xls").Activate
	Sheets("Builder's Bene 2008").Select
	Range("E4:F18").Select
	Selection.Copy
	Windows("MasterX2.xls").Activate
	Sheets("Single").Select
	Range("E17:F17").Select
	ActiveSheet.Paste
End If

As well as simplifying the code as described, you could use a function to carry out the copying, passing the variable element (the range to copy, in this case) as a parameter to the function. Since it seems that only one of your IF statements would ever be true, you could use a Select Case statement instead of multiple IF statements.

If the value in E4 always equates to the name of the target sheet, you could again pass this value as a parameter to the function rather than test for it each time. Other methods of dealing with this situation are available!

A couple of small suggestions, I’m sure others will have more to offer.

I’ve got this code in the AfterUpdate, and I’m still only getting 1 instance of the Part No (I haven’t worked out how to get the other data in yet)

Code:

Private Sub BATCH_No_AfterUpdate()
[Deleted]
strSQL = "SELECT tblXrayImport.PartNumber, tblXrayImport.Qty, tblXrayImport.BatchNo, tblXrayImport.[Xray No]FROM tblXrayImport WHERE [BatchNo] = '" & strSearchBatchNo & "';"
Set rsGetIWO = CurrentDb.OpenRecordset(strSQL)

If Not rsGetIWO.EOF Then
	 rsGetIWO.MoveFirst
	 Do While Not rsGetIWO.EOF
		 varIWO = rsGetIWO(0) ' only one field is returned from the recordset, so zero is appropriate
		 Me.PART_NO = varIWO 'do whatever you want with the value here, before moving to the next item
		 rsGetIWO.MoveNext
	 Loop
'Else
   ' add any more error handling here in case value not found
End If

End Sub

I should be seeing the part number and Batch No on 4 line entries (as in tblXrayImport) shouldn’t I?

Rob,

OK, what’s happening is the code is looping through the entries nicely, but…. it is just overwriting the value on screen every time. There’s nothing to write/move to a new record. There’s at least two ways you could approach this problem. If I was starting from scratch, I’d ask for input into unbound controls rather than into a subform. I’d then use the data to append rows directly to the table, and requery the subform to display the new data.

If we stick with your original logic, you need to update the existing record the first time round the loop, and then append a new record every subsequent time round the loop. So, you need either a counter within the loop, using this to decide which action is appropriate:
Start intCount at 0 outside the loop.

Code:

intCount = intCount + 1
varIWO = rsGetIWO(0) 
If intCount =1 then
   'write to the existing record
Else
   'write to the new record
End If

Or, you can use a boolean (True/false) variable, that starts off false and becomes true AFTER the first record has been written.

You’ll probably want to add the Date and Order Number from the original record into any new record, so take them into variables early on in your code. Once you move to a new record, they’re not available!

Not a one-line answer, but there are several steps involved. Go for it!

Jules

Rob,

A recordset is just (?) a query. Design your query in the query grid, change to SQL view, copy the SQL, and paste it where you need it.

Rob,

the code currently is in the After Update event of the Batch No field. I see no reason to change this. To replace the ‘alternative’ part of your original Dlookup(), add a second search as shown. This assumes that you do not need to search in the second table if a record is found in the first table.

Code:

If Not rsGetIWO.EOF Then
		rsGetIWO.MoveFirst
		Do While Not rsGetIWO.EOF
			varIWO = rsGetIWO(0)
				'do whatever you want with the value
			rsGetIWO.MoveNext
		Loop
Else   ' EOF, i.e. no records found in first table
	   'perform a similar search on tblxRayImportAli

End If

Regards,

Jules

Noted, thanks.

Rob,

you need to forget about dlookup() and start using recordsets. Something along these lines, copied for each data item, should do the trick:

Dim rsGetIWO As DAO.Recordset
Dim strSearchBatchNo As String
Dim strSQL As String

strSearchBatchNo = Nz(Me.BATCH_No) ‘ put the value of the screen’s control into a variable
If strSearchBatchNo = “” Then ‘ an entry has been deleted, not added
Exit Sub
End If

strSQL = “SELECT [Xray No] from [tblXrayImport] WHERE [BatchNo] = ‘” & strSearchBatchNo & “‘;”
Set rsGetIWO = CurrentDb.OpenRecordset(strSQL)

If Not rsGetIWO.EOF Then
rsGetIWO.MoveFirst
Do While Not rsGetIWO.EOF
varIWO = rsGetIWO(0) ‘ only one field is returned from the recordset, so zero is appropriate
‘do whatever you want with the value here, before moving to the next item
rsGetIWO.MoveNext
Loop
Else
‘ add any more error handling here in case value not found
End If

(indents seem to get lost in the posting, add them as required)

Regards,

Jules

Said,

in Project 2003 the start date can be found under Project Information. This is available from the Project menu, and can be edited. There are some other interesting settings available from this menu option. Have a play.

Best of luck,

Sal,

If the cause was the ampersand, the problem should also appear after TEST_TAG_4 as well. Since it doesn’t, could the value returned by TEST_TAG_5 actually have a trailing space?

MS Fan is probably more on your wavelength than I am, so I’ll duck out here. Just to say that you can save the SQL Server password when you link to the SQL Server tables via the ODBC connection, so users are never prompted and do not need to know the password. The ODBC connection must be set up on each PC, of course. Another option, but perhaps not suitable for you.

A happy new year to you and all Loungers!

Kathryn,

I assume you have security concerns that are forcing you down this route. If your clients are on a secure network, have you considered the simpler option of using a SQL Server login?

Regards,

(Edited by HansV to make URLs clickable – see Help 19)

You’ll see this, or a similar error, until all but one person has closed the database.
To split the front and back ends, the simple way is to take a copy of your file so there are two identical databases. Delete everything but the tables from one copy. Put this on a shared network drive. From the other copy, delete the tables. Then create a link to the back end tables on the network drive. Give a separate copy of the front end to each user. Do not move the back end, or the links will break.

There’s an old article about this at http://support.microsoft.com/kb/142467%5B/url%5D. A more up to date article is at http://support.microsoft.com/kb/304932%5B/url%5D.

JJ,

First question is, have you split the front end from the data tables? This is recommended practice for several reasons. That way, each user can have their own copy of the front end.

If you have not gone down this route, then most probably one person has opened the database ‘exclusively’. The default mode is normally ‘shared’, but this can be changed. Or, they are in the process of making changes to database objects and this has locked the system.

Regards,

Hi Twinky!

Of course Hans is correct. He’s one of the most knowledgeable and helpful people around. But since you’re new to the forum, I wonder if you perhaps haven’t phrased your question correctly or supplied sufficient detail. You haven’t, for example, told us what you want to do with the data once you’ve selected it.

As an exercise, I’ve assumed you might want to move the data elsewhere; perhaps even into a new table. Here’s some simple code that runs through all the queries in the database and for each query, creates a new table holding the top ten records.
I’ve deliberately left out docmd.setwarnings false/true for debugging purposes and to allow you some control. The code would also need better error trapping in case the table already existed. Is this any help?

JulesG

Private Sub cmdCopyQueries_Click()
On Error GoTo aEscape

Dim dbCurrent As DAO.Database
Dim strQueryName As String
Dim strSQL As String
Dim qdf As QueryDef

Set dbCurrent = CurrentDb

For Each qdf In dbCurrent.QueryDefs
strQueryName = qdf.Name
strSQL = “SELECT TOP 10 ” & strQueryName & “.* INTO tbl” & strQueryName & ” FROM ” & strQueryName & “;”

DoCmd.RunSQL strSQL

Next qdf

Exit Sub
aEscape:
MsgBox Err.Description
End Sub