Replies

Because tblServiceZone is where the data would be retrieved in future. I have to do an autopopulate, so the user doesnt have to type in all the details. State is an optional field, its hardly used but I have to put it there. (ie state is not different from terminal, zone and Town.

Oh ok, basically what i want to do is quite simple.
I have a table for storing transactions (tblServiceZone). A user has to select a location (eg Australia, Sydney) from a combo box, and that would automatically fill in the fields. These fields are Terminal and Zone in tblServiceZone. Such data would be retrieved from tblZoneList, but stored in the fields in tblServiceZone.
In the Access help, my problem comes under “About AutoLookup queries that enter data automatically”,,,i’ve tried using that method, but i’m having trouble in applying it.

Oh ok, basically what i want to do is quite simple.
I have a table for storing transactions (tblServiceZone). A user has to select a location (eg Australia, Sydney) from a combo box, and that would automatically fill in the fields. These fields are Terminal and Zone in tblServiceZone. Such data would be retrieved from tblZoneList, but stored in the fields in tblServiceZone.
In the Access help, my problem comes under “About AutoLookup queries that enter data automatically”,,,i’ve tried using that method, but i’m having trouble in applying it.

Hi Hans,
I’m a little bit confused on this. Its the structure of the query that i need help on.

Hi Hans,
I’m a little bit confused on this. Its the structure of the query that i need help on.

sorry, here is the attachment

Thankyou so much Hans, your explanation and db was clear-cut and has helped me get a headstart on this access report phenomenon.
Greatly appreciated

Thankyou so much Hans, your explanation and db was clear-cut and has helped me get a headstart on this access report phenomenon.
Greatly appreciated

I’ll put this post on hold, as i gather more info to explain the issue with better clarity.

hehe, right now, I’m under so much pressure, if it works, then I’ll be over the moon. If you’ve got time, I’ve posted another question, it would be appreciated if u could look into it, thanks Mark.

Thanks Mark, the approach I tried was this way, and it seems to work ok so far, so fingers crossed .

‘ update receiver
strsql = “select * from tblReceiver where 0 = 1”
set rs = server.CreateObject(“ADODB.Recordset”)
rs.open strsql, conn, 1, 2
rs.addNew
rs(“ConsignmentID”) = Request.Form(“ConsignmentID”) & tmpFld
rs(“name”) = Request.Form(“receiverName”)
rs(“business”) = Request.Form(“receiverBusiness”)
rs(“address”) = Request.Form(“receiverAddress”)
rs(“workNo”) = Request.Form(“receiverWorkNo”)
rs(“homeNo”) = Request.Form(“receiverHomeNo”)
rs(“mobileNo”) = Request.Form(“receiverMobileNo”)
rs(“email”) = Request.Form(“receiverEmail”)
rs.update
set rs = nothing

‘ update sender
strsql = “select * from tblSender where 0 = 1”
set rs = server.CreateObject(“ADODB.Recordset”)
rs.open strsql, conn, 1, 2
rs.addNew
rs(“ConsignmentID”) = Session(“userid”) & tmpFld
rs(“name”) = Request.Form(“senderName”)
rs(“business”) = Request.Form(“senderBusiness”)
rs(“address”) = Request.Form(“senderAddress”)
rs(“workNo”) = Request.Form(“senderWorkNo”)
rs(“homeNo”) = Request.Form(“senderHomeNo”)
rs(“mobileNo”) = Request.Form(“senderMobileNo”)
rs(“email”) = Request.Form(“senderEmail”)
rs.update
set rs = nothing

Thanks a million Pat. It looks really good.
Nope, free issue and cutsht are not similar at all. Two different columns.
So I’ve changed it back accordingly.
I only had 3 hours of zzz’s,,,when u have something like this on your mind 24/7, then you’ll start having
dreams of it too,,,,aggghhh, gonna try and get more zzz’ s now

Anyway, I’m soooo tired, havn’t slept yet. I really have to learn to sleep early and get out into the sun more. Not so easy though with all this on top of me. If you’ve got any advice for my above question, then once again, a thousand appreciations Pat. Take care, speak to you later.
Mark the zombie is off to sleep zzzzzzzzzzzzzzzzzz

Guess what Pat? ,,,,,,,,,,I DID IT !!!
I’ve attached the working db, how i did it, i really am so tired to recollect, but it took me only 2 hours, of studying the code line by line.
Out of the 5 excel sheets, only 4 were logged into the errors table, and the other one was bypassed, but i’m suspecting it might have something to do with it being watermarked as Deleted, although thats just a rough guess.
Is there a simple way of identifying/matching the excel sheets in a folder, with those in a table, and then displaying the ones that are not in a table? (but happens to be in a folder). Is that possible?, I was thinking this might act as a last line of defence incase the errors table does not pick up the unsuccessful excel sheet.

I got an extension on this, mainly because the supervisor is away, so the deadline has been extended till tuesday tommorow when he gets back.
Mannn, I’m developing a positive relationship with access now, I guess I don’t need to curse it as much as before..
Woooohhoooo. You the Man Pat, your access skills make all my I.T. Uni Lecturers look like amateurs.
13 weeks in a semester and a student hardly learns anything in depth such as this.

Sorry, let me rephrase what I said earlier, there is actually calculations in the weights field. But that is no different for the other pmls that imported smoothly.