Search criteria query

Hi everyone



I am creating a small database for managing publications within our business and have added a search query to report back results based on the search criteria of the user.



The problem I have is that I am a beginner using Access - learning pretty much as I go on so I am stuck with something.



When I enter the search criteria into the text box and click my Run Search Query button, I get a popup dialog box which shows the criteria I entered in my Query:



Forms!searchform.qpublication1



The criteria entered on the Query itself in design view is as follows:



Like [Forms]![searchform].[qpublication1] &"*"



Is there a way of removing this popup dialog box which I basically just click Ok on the box which continues the search query?



Thanks in advance

Rich

Search criteria query

Hello everyone

New member of the forum - looking to learn a lot more about Access and the way it works!! :)

Hello everyone

Hi from Wales

Hi to everybody on the site. I used Access up to 5 years ago but have become a little rusty. I have recently found a new job where the main work involves designing Access databases and would appreciate any help I can get from fellow users. Thanks for providing such an excellent resource.

Hi from Wales

Update Query Syntax Issue

Hello all.



Hoping someone may be able to help with an error I keep getting with the syntax of an update query I'm putting together. I'm sure it's an embarrassingly basic mistake I'm making, but I'm fairly new to Access and can't seem to find a solution. Have searched this and other forums for answers to similar questions but still can't resolve.



I have an update query that updates a table containing students' test results. The original SQL, which predates when I first took over managing this database is as follows:

Quote:

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID], [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A],[tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A]

That's just an excerpt; it goes on much longer because there are around 20 fields, but obviously the syntax of the '[tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A]' section just repeats with different field names, separated by commas



The problem with this is that the table containing the updates [tbl_Assessmentyr1] is a record of only new grades achieved within a recent time period. Therefore it will contain blanks in some fields where a student may already have a grade recorded in the main table [tbl_*ModuleGrades_Yr1] because they took that particular test in a previous time period. So you could easily overwrite valid results with blanks.



To fix this I tried to amend the syntax so that will only make updates where the field in the main table does not already contain a result. I used the following:

Quote:

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] is Null

That works fine if my statement deals with just a single field, but if I do this for all 20 fields that the statement originally covered, i.e. so it reads like this...

Quote:

UPDATE [tbl_Assessmentyr1] INNER JOIN [tbl_*ModuleGrades_Yr1] ON [tbl_Assessmentyr1].[Student ID] = [tbl_*ModuleGrades_Yr1].[Student ID] SET [tbl_*ModuleGrades_Yr1].[Student ID] = [tbl_Assessmentyr1].[Student ID], [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] = [tbl_Assessmentyr1].[Mod1 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod1 Prelim A] is Null, [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] = [tbl_Assessmentyr1].[Mod2 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod2 Prelim A] is Null, [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] = [tbl_Assessmentyr1].[Mod3 Prelim A] WHERE [tbl_*ModuleGrades_Yr1].[Mod3 Prelim A] is Null

...and so on I get 'syntax error: comma in query statement'. There were no such problems before, so I assume the problem lies in the syntax of the WHERE part of the statement that I've added, but I can't figure out where I've gone wrong.



Very grateful for any assistance.

Update Query Syntax Issue

hello all...

i m new here and i student of vit university india

hello all...

InitialiseEvents problem Plz Help

Private Sub Form_Open(Cancel As Integer)

InitialiseEvents Me

End Sub



Quesion !



when Run this Function on form all form ctrls affected but not Subform ctrls plz help ???????

what Could Change to apply it on Subform Ctrls ?????





Public Function InitialiseEvents(frm As Access.Form)

Dim ctl As Control

For Each ctl In frm.Controls

With ctl

If .ControlType = acComboBox Then

.OnGotFocus = "=HandleFocus('" & frm.Name & "', '" & .Name & "', 'Got')"

.OnLostFocus = "=HandleFocus('" & frm.Name & "', '" & .Name & "', 'Lost')"

End If

End With

Next ctl

End Function



Public Function HandleFocus(ByVal strFormName As String, _

ByVal strControlName As String, _

ByVal strChange As String)

Static lngForeColour As Long

Static lngFontWeight As Long

Static lngBorderStyle As Long

Static lngBorderColour As Long

Static lngBackStyle As Long

Static lngBackColour As Long



On Error Resume Next

With Forms(strFormName)(strControlName)

Select Case strChange

Case "Got"

' Save current configuration.

lngForeColour = .ForeColor

lngFontWeight = .FontWeight

lngBorderStyle = .BorderStyle

lngBorderColour = .BorderColor

lngBackStyle = .BackStyle

lngBackColour = .BackColor



' Set required configuration.

.ForeColor = vbBlue

.FontWeight = 700

.BorderStyle = 1

.BorderColor = vbRed

.BackStyle = 1

.BackColor = vbYellow



Case "Lost"

' Restore saved configuration.

.ForeColor = lngForeColour

.FontWeight = lngFontWeight

.BorderStyle = lngBorderStyle

.BorderColor = lngBorderColour

.BackStyle = lngBackStyle

.BackColor = lngBackColour



End Select

End With



Err.Clear

End Function

InitialiseEvents problem Plz Help

Number Sequence

Hi Folks



I am in the process of starting to make a small database to record orders for door access cards. easy so far, however I need to record the number required as well as the number range start and end. i.e. 200 cards starting 1 - 200. Next batch would be 201 - + Quantity. I have tried messing with a query but don't really know where to start. I want it to auto add the number from the previous issued cards to give me the number range. I have done this in Excel but in Access seem to have got a bit lost. Any help would be much appreciated.

Number Sequence