Subform/ Subreport Query Data Row Number Reference

Jul 20, 2005

I am new to this and i may be going about this the wrong way but what i am doing seems to do the job until now.

I am making a form that has a combo box, a few text boxes and a subform subreport on it.

The idea is that the first thing a user does is to select a name form the combo box. this name is then stored as a sring and used to set the forms record source and then the subform's (called window) source object. the string is slightly modified during the process to do this. That part works fine.

The problem arose when i included text boxes to show infomation from the selected Query thatisant show in the window (subform).

After the user selects a name and the name is used to set up the record source and the source object for the text boxes.it then displays in the text boxes the records extra data. however it only displays the first row extra data.And when other rows in the query table are selected the data in the text box stays set as the first row.


I know that this is because i haven't programed in an event that makes the text box data update when the user selects a new row.

i also realise that i would need to have some way of know what row of the quiery table the user is looking at to be able to update the text boxes.

my questions are

Is there a way to tell what row of a query table in the subform is selected ?

Is there an event similar to on selection of row or something like that that i could use to reload the text boxes?

and finaly

am i going about this the right way?

Any input on this would be greatly appreciated :)

View Replies


ADVERTISEMENT

Queries :: How To Add Sequential Dates With Reference Number With Append Query

Apr 7, 2015

I want to populate a table with field:date and field ref nr.(7 fixed ProductID's)

But only weekday and to 3 years from now. (every weekday)

Example

field:ID | date | fixed.ref.nr
--------------------------------
1 | 7/4/2015 | 1001
2 | 8/4/2015 | 1002
3 | 9/4/2015 | 1003
4 | 10/4/2015 | 1004
5 | 13/4/2015 | 1005
6 | 14/4/2015 | 1006
7 | 15/4/2015 | 1007
8 | 16/4/2015 | 1001
9 | 17/4/2015 | 1002
10 | 18/4/2015 | 1003
.... (ID field is autonumber field and not important..)

How to do this with a append query or code?

View 1 Replies View Related

Query Criteria Cannot Reference Subform

Jul 19, 2007

Hi,

Trying to run an append query for a specific record on a subform but won't recognise the subform control.

The Master form is fmSickEdit do I have to reference the Master file in the SQL?

The SQL for the query is:

INSERT INTO tblDisLetter ( SickID, DateAdvisedDisciplinary )
SELECT tblSick.SickID, Date() AS [Date]
FROM tblSick
WHERE (((tblSick.SickID)=[Forms]![fmsubSickListEditVersion]![SickID]));


Thanks

View 2 Replies View Related

Modules & VBA :: Reference To Control In Subform From Query

Oct 10, 2013

I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER").

View 10 Replies View Related

Auto Sequence Number For Reference Number

Dec 8, 2005

The title probably doesn't give an idea of what I need, so here goes.

We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.

example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7

I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".

I have a query that generates the first portion (date, customer, and initials):

SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;

But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)

Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.

Thanks.

Gary

View 3 Replies View Related

Noob With Textbox And Subform/subreport Problems

Aug 4, 2005

I am making a from that has a set of combo boxes that allow the user to search through a table. the results are displayed in a subform / subreport window that is set to a query. the search works by changing the SQL of the query. I have spent weeks on getting this far and i have decided to just show some key infomation in the subreport window.

What i want to do now is to display further infomation from the query row that is selected by the user in a textbox on the form.

i have been looking in the access VBA help,an access programming book and in the forum .And found that it is something to do with the recordset and making the text box bound but i cant find any examples where it is applied in this way.

the problem is (i think) that i would like the text box data to change when the user selects differnt rows so would this change what the textbox is bound to. :confused:

I hope it is possible and if anyone can help it will be greatfully recived

Trees

View 1 Replies View Related

Reports :: Syncing Current Subform To Subreport

Aug 12, 2014

I am trying to send the current record and the current subform record to Reports for printing.Each record on the Form can have multiple records in the subform, however I only want to print the Main Form and the Current subform.Form / subform are linked on EquipID and EquipIDfk and are working correctly.Report / subreport are linked the same way and work correctly.When I try to use the DoCmd.OpenReport... It is printing the data from the main form plus all subform records related to the main record.I have tried the following Sub to filter the subreport without success.

Private Sub cmdPrintRecord_Click()
Dim strWhere As String
Dim strLook1 As String
Dim strLook2 As String
strLook1 = Me![EquipID]
strLook2 = Me![subfrmInspectionReport].Form![InspectionFindingspk]

[code]...

Using Debug strLook1 and strLook2 both show they have the correct relevant record numbers and the value stored in strWhere is: [EquipID] = 745 And Reports![RptPrintRecord]![subrptInspectionReport].Report![InspectionFindingspk] = 8. This only prints an empty report. It seems that when I try to reference the subreport it goes pear shaped.

View 5 Replies View Related

Reference Number

Aug 29, 2006

Hi guys/gals

My primary key, RefNumber, is a sequentual Autonumber field. I want to creat my own Custommade autonumber field, but the catch is:
My autonumber field should have any of the following (selectable by the user from a combo box from a Form) attached in front of the number: DPR,DPZ,DFE,DGL,DMI,DGJ or DPE.
ie: the autonumber is at 14. The user selects DFE, but this is only the seconde choice made of DFE.

So the end product should be: RefNumber = 14
.........................................DistrictR efNumber = DFE2 or DFE002

Help please?
Thanks in advance

View 5 Replies View Related

Reference Number Question

Apr 26, 2007

Hi, very new to Access and wanted to check something was possible before i proceed with it,

basically i want to assign a reference number to my records automatically based on data that has been entered by the user, looking something like this

AE/13MAY07/01

AE is a standard thing and will never change.

13MAY07 is 13th May 2007, which is a date which has been entered by the user for when a specific piece of work is to start. There will be a Yes/No box next to where they enter the date saying whether or not they want a new reference number assigned. If they select Yes, the reference number will be populated with the date specified.

01 will be a number which goes up in increments of 1 based on the MAY07 detail. The number will start at 01 for every month/year, eg

MAY07/01
MAY07/02

JUN07/01
JUN07/02

MAY08/01
MAY08/02

Hope someone can understand what im trying to do and can help.

Thanks in advance :)

View 1 Replies View Related

Customised Reference Number

Dec 7, 2007

Hi there

Just a quick problem i need a solution to.

I just created a database, but i want it to automatically created reference number for example. LAPSS001 n continue creating

any tips pls?

View 3 Replies View Related

Reference Number Generation

Oct 4, 2006

Hi Guys

Dont quite know if this is the right place to post this but here goes any way

Im creating a database where customers can submit a question, it centres around a form where a user enters various information such as initials, telephone number, and various selections from from down boxes.

I have placed a button at the bottom that saves the record, what i want is when that button is pressed for a box to pop up giving the user a unique reference number which is made up of the information they have entered, such as
FIRSTINITIAL-LASTINITIAL-DATE-INCREMENTALNUMBER
I want this number then to be stored along with the record so it can be searched for by this number.

I have done searches for similar questions but cant find one that matches what i want to do

Thanks in advance

View 2 Replies View Related

Creating Reference Number From 3 Fields

Oct 19, 2005

Hi there,

I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.

xxxx or yyyy/lookup in 2 table/autonumber

I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.

I have limited knowledge particularly of VBA coding

Any help much appreciated

View 1 Replies View Related

Reports :: Customize Reference Number - Continuous Count

Dec 16, 2013

how to create a Report with Customize Reference No. Reference Number should be continuously adding 1 count every time a user will print report.
example:

On dec 16, morning..... reference no: THS-01 (1st print)
dec 16, afternoon...reference no: THS-02 (2nd print)
dec 17 morning.....reference no: THS-03 (3rd print)
dec 18 morning.....reference no: THS-04 (4th print) etc....

THS-XX is my reference number and it will continuously counting. Is this possible in MS Access 2007 Report? If Yes? How can i do it?

View 3 Replies View Related

Modules & VBA :: Automate Generation Of Reference Number Incrementally By 1

Jul 29, 2013

I am trying to automate the generation of a reference number incrementally by 1.

In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.

In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.

So for example:

tblBooking:
autBookingID: 1234
tblBookingStops:

[Code]....

I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.

View 5 Replies View Related

Queries :: How To Remove Duplicates On Reference Number Field

Aug 28, 2013

I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?

View 3 Replies View Related

Subform Reference

Apr 7, 2008

Hi,

I have a form (frmSWL) with a subform container on it (subfrom1) which has a source object called frmSite. How do I reference frmSite from another form so I can apply a filter to it??

I am trying:

forms!frmSWL!frmSite.Filter = "......."

, but it is not working.

Thanks,

View 3 Replies View Related

Forms :: Generate A Unique Reference Number Record Wise

Jun 10, 2015

Need to generate a unique reference number each record wise while data entering by a form. How to do this by using a command button ? After entering all fields related to the record, when click on the command button, unique reference number for that record need to be generated and save with all other data of the records. in a later event, need to recall the record by using this reference number and also need to make relation ship with some other table by using the reference number.

View 6 Replies View Related

Making Dispatch Notes - Generate Reference Number Using AutoNumber

Aug 3, 2011

I have made a database for a repair workshop, where you book in and book out repairs.

I am having a problem as we need to make a Dispatch Note daily with a list of all booked out repairs for that day and for that branch.

This is all working so far. However, we need to generate a reference number using AutoNumber (Wshop0001, Wshop0002, etc) and keep a record of this - how can this be done with keeping ONE reference number per day per branch?

View 14 Replies View Related

Reference A Field On A Subform

Feb 26, 2005

I have 10 fields on a subform. they are named value1 to value10 consecutively. I would like to loop thru them, and get their values to use in subsequent events. I tried the following to reference them...

Dim iLoop as integer
Dim fldVal as control
Dim ItemValue as string

For iLoop = 1 to 10
fldVal = ("Forms![Form 1]![Subform 1]!value" & iLoop)
ItemValue = fldVal
Next iLoop

I also tried a couple of other variations, but cannot get this **** thing to work. Any help would be appreciated.

View 2 Replies View Related

Subform Reference Each Row Event

Feb 23, 2006

I have tried and searched as much as i can.

I have a continous subform, a text box which has a date.

For each row i want a button to be visible where the date is blank. If the date is filled in No button. So i thought need an event. But where?

Can you reference row values?

Here's the code, where would it go?

If txtEndDate = Null Then
cmdCheckout.Visible = True
Else
cmdCheckout.Visible = False
End If

View 5 Replies View Related

Forms :: How To Reference One Subform From Another

Oct 24, 2013

I have the following bit of VBA which works like a charm :

Code:
With CallNotes.Form.RecordsetClone
.AddNew
!CustomerRef = Me!CustomerRef
!Who = Forms.markswitchboard!Text52
!Date = Now()
!Notes = "Final Deposit Due on" & " " & FinalDepositDueDate
!DiaryDate = FinalDepositDueDate + 1
.Update
End With

However, if I try to use this code on a different tab control it just doesn't work.I get Runtime Error 424 object required.I guess I am asking how to reference one subform from another?

View 2 Replies View Related

Reports :: Show Subreport With No Data

Jun 16, 2014

I have a main report with several subreports. Some subreports have other nested subreports. all of the reports and subreports are based on queries. when a query returns no values, i would still like for the reports and subreports to display in print preview. (currently, they display in report view but not in print preview.)

I tried a few things:

1) I set all report section can shrink options to "No" - (this did not work)

2) I set the can shrink options for all textbox controls, within each report section , to "No" - (this did not work)

3) I moved all controls from the details section of the subreport to the report header section - (when I did this, I got the subreport to show in print preview when the query for a single subreport returns no values but when i have a subreport with nested subreports that have no value, none of them show up in print preview)

I've searched...moving the controls from the details to the report header was the best thing i could find, but again, this only works with a single subreport and not with multiple nested subreports.

View 8 Replies View Related

Reports :: Suppress SubReport With No Data

Jul 25, 2015

I have a report that calls a subreport. In most instances, the subreport will have no data and I won't want to show the subreport. I've tried various ways to deal with this:

1) Make the subreport property on the main report the minimum height and set CanGrow = Yes
2) Use the Report_NoData event on the subreport and add the code 'Cancel = 1'
3) Make all the fields on the subreport the minimum height and set CanGrow = Yes

None of these seem to work. I don't have any headings on the subreport, so the only price I'm paying is adding a gap onto the main report the size of one detail line of the subreport. Still, it leaves the report lacking professional quality.

View 4 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Reference A Control In A Subform From A Module

Aug 4, 2005

Hi,

I'm trying to reference a control in a subform from a module but I keep getting errors!

Here's what I've tried.. and what error I've been getting:

First try:
GetCboEntries = Forms.frmMain!frmSub.Form![cboEntries].Column(1)
(where frmSub is the name of the actual form in the subform control)
Error: Object doesn't support this property or method

Second try:
GetCboActivities = Forms.frmMain!chdMain.Form![cboEntries].Column(1)
(where chdMain is the name of the subform control)
Error: Object doesn't support this property or method

Third try:
GetCboEntries=Forms!NameOfMainForm!NameOfSubform![cboEntries].Column(1)
Error: Run-time error '2450':
Microsoft Access can't find the form 'frmMain' referred to in a macro expression or Visual Basic code.

Any help would be much appreciated!

Thanks in advance!

View 1 Replies View Related

Modules & VBA :: Reference To A Table On Subform

Oct 22, 2013

I have a form which have a sub form. In the sub form I have a the direct table to update data on it.

Example:

The form has the name of the project and the subform the name of one of the items of the same project and the table present the documents for the particular item.

The users have the option to add or edir docs to this item.

If I would like to reffer to a value they are entering on this table in VBA . how can I do that?

(I would like to present a msgbox when they are entering a date that isn't right on the table for this item.)

By the way, I can't use validation rule because I have a different dates needed per item.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved